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


  • jmyzk_cloudsmart_jp
    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.

    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 [email protected].

    It was a lot of work.😅

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!