Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Calculating Time without using Date field type

Solved
Jump to Solution
90 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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
kuovonne
18 - Pluto
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().

See Solution in Thread

3 Replies 3

Not sure I fully understand the hesitancy with date/time fields... Is there a reason that this would not work for you?
Screen Shot 2023-01-18 at 12.34.03 PM.png

The formula in the Difference field is

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

kuovonne
18 - Pluto
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().

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