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!!
Best 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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!