# Is there a way (function) to make time range consistent?

Options
✭✭

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:00-10:00AM, or 9-10 am).

• ✭✭✭✭✭✭
Options

Hi @j.hung

You can use the TIME function to convert the 12-hour clock (AM/PM) to the 24-hour 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 "1-3 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)