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 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!