Extract a MM/DD/YY Date from M/D/YYYY HH:MM

I'm looking for some formula help to convert a date format.

The date column I'm referencing is coming in from another source, and is formatted as M/D/YYYY HH:MM - here's some example data from the sheet:

I need to compare these dates to a user-set date (column = Prior Run Date) in my sheet, which is a Date column type (restricted to dates only) to see if they are greater than/equal to the date in the Prior Run Date. However, the only way to compare these two is to have them both be dates in MM/DD/YYYY format, from what I can tell.

I had a formula that was working until today… I think because the month changed from a single digit to double digit (yay October!), and as a result of that formula breaking, an automation & data shuttle on my sheet ended up deleting everything. Luckily I have a backup archive in Excel, so was able to reimport my data.

However, to keep this from happening again, I'd like to find an efficient formula that will work.

My current attempt is to pull our the month, day, and year via formula into their own columns, the concatenate those columns together as a date.

This looks like a date, and the column type is set to date, but it is recognizing this as text instead of as a date.

I have also tried = date(year, month, day), but am getting an error on that because the year/month/day columns are text, not numeric. I get this error even if I leave the column type as Text/Number

I know this is doable, since I have had it working for the past 9 months… any suggestions? Thank you!

Find more posts tagged with

Formulas

dates

Accepted answers

KPH

Hi @krsch88

You are super close and (IMO) have done the hard part splitting the date into the 3 columns. I don't think the problem is the change to a double digit month. I think it is the leading 0 on the day. To get 01 to appear rather than 1, a ' is at the beginning of the Day cell. That ' is not expected in the DATE function.

If you put the day part into a VALUE function it will convert the 01 to 1 and should solve the problem.

=DATE(Year@row, Month@row, VALUE(Day@row))

For good measure, I'd do the same to month in case the new way of doing that has changed 9 to 09.

=DATE(Year@row, VALUE(Month@row), VALUE(Day@row))

You don't need to concatenate the "20" for the year.

All comments

KPH

Hi @krsch88

If you put the day part into a VALUE function it will convert the 01 to 1 and should solve the problem.

=DATE(Year@row, Month@row, VALUE(Day@row))

For good measure, I'd do the same to month in case the new way of doing that has changed 9 to 09.

=DATE(Year@row, VALUE(Month@row), VALUE(Day@row))

You don't need to concatenate the "20" for the year.

krsch88

Thank you @KPH ! Looks like VALUE() was the solution! I ended up switching my year/day/month columns to be =VALUE() and then was able to use the =DATE() function in my "shortened date" column.

KPH

Great news, glad I could help.

guillermogeol

Hi mate, might be late on this, but I had the same problem a while ago:
You see how excel sometimes converts dates into numbers for no apparent reason?
If you do a simple
=INT()
it will remove the decimal part of that number (which is the hh:mm of the backend.
=INT(A1)
will return
dd/mm/yyyy
only
no need to split cells into year, month day, and concat.
Cheers,
G

KPH

Thanks for the suggestion @guillermogeol

Unfortunately, the INT method cannot be used to extract a date from a timestamp in Smartsheet as the original date is stored as a text string, not a number with decimals, as it is in excel.

guillermogeol

@KPH
Ahaaaa, I gotcha.
Well, in this example would a simple =LEFT(A1,10) work?
(the ten leftmost chars i.e. dd/mm/yyyy)
Even if some dates do ont have a leading 0 (like 9/10/2023), the formula would copy the space folowing the date: 9/10/2023 ←(here); for the purpose of comparing duplicates like @krsch88 was looking for.
(Not a one size fits all, but for this particular task, it would simplify the amount of calculations)

Paul Newcome

@guillermogeol That still will not work for two reasons.

First, if the month and day are both single digits, you will pull in the first character from the time portion. If you want to use the LEFT function, I suggest leveraging the FIND function to find the space and use that output for how many characters to pull.

=LEFT([Date/Time Column]@row, FIND(" ", [Date/Time Column]@row) - 1)

Second, the LEFT function in Smartsheet outputs a text string. It will LOOK like a date, but it will not act like a date for later use in things such as metrics and automations. This is the same issue as the original post when concatenating.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.