# Re: Calculating Time without using Date field type

Solved
1128 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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

1 Solution

Accepted Solutions
18 - Pluto

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

3 Replies 3
8 - Airtable Astronomer

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

18 - Pluto

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

6 - Interface Innovator

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