Converting a date/time text format to a simple date format

Kristin Stoddard

Hey everyone - I am trying to convert a text field that reads 2024-09-25 15:35:27 to a simple date format, either 2024-09-25 or 9/25/24. I've tried using a helper column to just pull out the text date string - =LEFT([Registration date]@row, FIND(" ", [Registration date]@row) - 1) in the hopes that would be convertible to a date format to use in formulas, but I'm having no luck on this second part of having it seen as a date. I thought this would be a simple easy task, but alas….

Any thoughts are appreciated!!

Kerry St. Thomas

If you aren't able to convert by changing the column type, you can parse out the data in your datetime string. Put this in as a column formula in a column of type Date, and you should be able to move forward as usual. (The formula assumes all dates are 4 digits long, and all month and days are 2 digits long each.)

=DATE(VALUE(LEFT([Registration Date]@row, 4)), VALUE(MID([Registration Date]@row, 6, 2)), VALUE(MID([Registration Date]@row, 9, 2)))

Good luck!

Kerry St. Thomas

If the text input can't be converted with a column type change, you can use a column formula INTO a date colum that'll do it. Using this formula, you won't have to just strip off the time. Basically, treat the datetime as a text string, and then isolate the year, month, and day separately. There's an underlying assumption here - year is always 4 digits, and month and day are always 2 digits.

=DATE(VALUE(LEFT([Registration Date]@row, 4)), VALUE(MID([Registration Date]@row, 6, 2)), VALUE(MID([Registration Date]@row, 9, 2)))

Good luck!

Kerry St. Thomas

If you aren't able to convert by changing the column type, you can parse out the data in your datetime string. Put this in as a column formula in a column of type Date, and you should be able to move forward as usual. (The formula assumes all dates are 4 digits long, and all month and days are 2 digits long each.)

=DATE(VALUE(LEFT([Registration Date]@row, 4)), VALUE(MID([Registration Date]@row, 6, 2)), VALUE(MID([Registration Date]@row, 9, 2)))

Good luck!

Kristin Stoddard

Thanks so much @Kerry St. Thomas!!! Worked beautifully!