Convert Date and Time to 24 Hour
What formula will help me convert date and time to 24 hour format?
The column I want to convert to 24 Hour format is "Time On-Site"
Answers
-
Hi @Mark P
Hope you are fine, could you please try the following:
1- Create a helper column (Text/Number) type call it [AM PM] and use the following formula for it and convert it to column format formula:
=IFERROR(RIGHT([Time On-Site]@row, 2), "")
2- Create a helper column (Text/Number) type call it [Hours] and use the following formula for it and convert it to column format formula:
=IFERROR(MID([Time On-Site]@row, (FIND(":", [Time On-Site]@row) - 2), 2), "")
3- Create a helper column (Text/Number) type call it [Minute] and use the following formula for it and convert it to column format formula:
=IFERROR(MID([Time On-Site]@row, FIND(":", [Time On-Site]@row) + 1, 2), "")
4-Create a helper column (Text/Number) type call it [Time On-Site(24h Format)] and use the following formula for it and convert it to column format formula:
=IFERROR(LEFT([Time On-Site]@row, 10) + " " + IF([AM PM]@row = "AM", Hours@row + ":" + Minute@row, VALUE(Hours@row) + 12 + ":" + Minute@row), "")
we can merge the formulas above to reduce the helper columns we added but i keep it like that to make it easier to be understood by the new members in smartsheet.
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
When I used the above formulas my military time did not work correctly for anything that fell between 12 PM and 1 PM so I changed the hour helper column to include an additional if statement to get it to convert correctly.
=IFERROR(IF(MID(Created@row, (FIND(":", Created@row) - 2), 2) = 12, 0, MID(Created@row, (FIND(":", Created@row) - 2), 2)), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!