Convert text (19-NOV-2020 11:30:38 AM) into date
Hello,
I am looking for a way to convert text in the following format: 19-NOV-2020 11:30:38 AM into a full date (with hour and minutes).
Any suggestions?
Thanks
Best Answer
-
HERE is a link to a post that has multiple time based solutions.
You are not going to be able to create a date/time stamp type column that directly functions as such, but you can use hidden helper columns to parse out the data and run the calculations. Feel free to browse the listed solutions and then let me know if you can't find one that works for you or if you need help adapting one of them.
Answers
-
Hello
Could you show us what date-time format you're needing? DD/MM/YY HR:Min AM/PM?
-
Hello @KDM
Yes, ideally it would be great to have DD/MM/YY HR:Min AM/PM format, to subtract the date of submission of a form and have the difference in days hours and minutes.
Thanks
-
Without a datestamp format column type, I have always had to split my date and time into two coumns - one a DATE formatted column, and the other as TEXT/NUMBER.
There are different ways to parse the date and time into columns. Because of timezone differences, I have found that stripping the time out directly is the most reliable approach for me
To get the date into a DATE column -
=DATEONLY(Created@row)
To get the Time into a Text/Number column -
=RIGHT(Created@row, (LEN(Created@row) - 9))
As I was setting up a sheet to do what I believe is similar work to yours, I found these posts by @Paul Newcome and to be extremely helpful. I'm sure other clever friends have posted as well.
Change Time into Military time
Duration between Military time
Can you calculate Time in Smartsheet?
cheers,
Kelly
-
HERE is a link to a post that has multiple time based solutions.
You are not going to be able to create a date/time stamp type column that directly functions as such, but you can use hidden helper columns to parse out the data and run the calculations. Feel free to browse the listed solutions and then let me know if you can't find one that works for you or if you need help adapting one of them.
-
Hello Paul,
Thanks for your message!
I'll browse your solutions and let you know.
Regards,
-
Hello @Paul Newcome I'll use the following:
=DATE(VALUE(RIGHT(LEFT([Order Date in text]@row, 11), 4)), INDEX({Data Tables - Month Number}, MATCH(MID([Order Date in text]@row, 4, 3), {Data Tables - Month Name}, 0)), VALUE(LEFT(LEFT([Order Date in text]@row, 11), 2)))
to convert Order Date in text (19-NOV-2020 11:30:38 AM) into a date (19/11/2020).
For the moment I leave the time aside, the difference in days between the two dates should be sufficient.
-
IF you are pulling the dates and converting them into actual date values, then you should be able to subtract one date from the other to get the difference.
-
Yes, is what I am doing and it's working fine.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!