Is there a way (function) to make time range consistent?
Here's an example. I'm trying to figure out if there's a way to, e.g., make the time range to be 09:00  10:00 (rather than 9:0010:00AM, or 910 am).
Answers

Hi @j.hung
You can use the TIME function to convert the 12hour clock (AM/PM) to the 24hour clock.
The demo solution below first shapes the inconsistent format with the SUBSTITUTE functions.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Primary Column]@row, "  ", ""), " ", ""), " ", ""), "0A", "0 A"), "0P", " P")
Then, split the result with "", using the text functions.
=LEFT([Shape with SUBSTITUTE]@row, FIND("", [Shape with SUBSTITUTE]@row)  1)
For "13 PM" or "1 AM  3 PM" type of data, add ":00: using the text functions.
=IF(CONTAINS(":", [From 1]@row), [From 1]@row, IF(CONTAINS(" ", [From 1]@row), LEFT([From 1]@row, FIND(" ", [From 1]@row)  1) + ":00 " + RIGHT([From 1]@row, 2), [From 1]@row + ":00"))
Then, finally, the formatted data is converted with the TIME function.
=TIME([From 2]@row, 1)
If you need a copy of this sheet, please contact me at info@cloudsmart.jp.
It was a lot of work.😅
You can avoid inconsistent input with a dropdown list like this.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!