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

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

Tags:

Best Answer

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭✭
    edited 11/15/24 Answer ✓

    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!

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Answers

  • Kerry St. Thomas
    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!

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭✭
    edited 11/15/24 Answer ✓

    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!

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!