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

j.hung

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).

Screenshot 2024-01-16 at 19.52.30.png

jmyzk_cloudsmart_jp

Hi @j.hung

You can use the TIME function to convert the 12-hour clock (AM/PM) to the 24-hour clock.

https://help.smartsheet.com/function/time

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)

If you need a copy of this sheet, please contact me at info@cloudsmart.jp.

https://app.smartsheet.com/b/publish?EQBCT=057a89ad7f06406da59605fcf0a3365b

It was a lot of work.😅

You can avoid inconsistent input with a dropdown list like this.