Calculating Time without using Date field type

[Noah_Coleman]

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

[Ron_Daniel]

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 · Accepted solution]

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

[Noah_Coleman]

@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!