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

krsch88

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!

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.

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.

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.