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 [email protected].
It was a lot of work.😅
You can avoid inconsistent input with a dropdown list like this.
Help Article Resources
Categories
Check out the Formula Handbook template!