Skip to main content
Solved

Calculating Time without using Date field type

N

Is there a way to calculate times without using a datetime field? For example, if I have a start time of 11:30am and an end time of 3:30pm, can I use a formula to calculate the difference without using a datetime field?

On the surface, I can do this by making my start and end times Duration-type fields, but I'd have to switch to a 24 hour format, which is less readable for me than a 12 hour format (Americans, amirite?). Also, the Start and End times will be used in concatenate formulas, so formatting becomes a real problem (Tuesday 8:30-11:30 becomes Tuesday 30600-41400).

 

Best answer by kuovonne

Are your start and end time fields currently text fields?

You can use a combination of DATETIME_DIFF() and DATETIME_PARSE().

DATETIME_DIFF(
  DATETIME_PARSE(
    "2023-01-18 " & {End Time},
    "YYYY-MM-DD h:mma"
  ),
  DATETIME_PARSE(
    "2023-01-18 " & {Start Time},
    "YYYY-MM-DD h:mma"  ),
  'seconds'
)

 

You can then format the formula field as a duration. Or if you want a different format, such as only hours or minutes, you can change the units in DATETIME_DIFF().

View original
    Did this topic help you find an answer to your question?

    3 replies

    R
    • Ron_Daniel
    • Inspiring
    • 104 replies
    • January 18, 2023

    Not sure I fully understand the hesitancy with date/time fields... Is there a reason that this would not work for you?

    The formula in the Difference field is

    CONCATENATE(
    ROUND(
    DATETIME_DIFF(
    {First date/time},
    {Second date/time}
    )
    * -.000277778
    , 2
    ) & " hours"
    )

    kuovonne
    Forum|alt.badge.img+17
    • kuovonne
    • Brainy
    • 5987 replies
    • Answer
    • January 18, 2023

    Are your start and end time fields currently text fields?

    You can use a combination of DATETIME_DIFF() and DATETIME_PARSE().

    DATETIME_DIFF(
      DATETIME_PARSE(
        "2023-01-18 " & {End Time},
        "YYYY-MM-DD h:mma"
      ),
      DATETIME_PARSE(
        "2023-01-18 " & {Start Time},
        "YYYY-MM-DD h:mma"  ),
      'seconds'
    )

     

    You can then format the formula field as a duration. Or if you want a different format, such as only hours or minutes, you can change the units in DATETIME_DIFF().

    N
    • Noah_Coleman
    • Author
    • Inspiring
    • 21 replies
    • January 18, 2023

    @Ron_DanielThe hesitancy is just because this doesn't need a particular date, just a time. I guess I could just pick a random date/time but I like that @kuovonne's formula keeps the time fields as just times and the date is hidden in the formula. Thanks for the help, everyone!

    Reply


    Most helpful members this week

    Terms & Conditions