Help

Re: Calculating Time without using Date field type

Solved
Jump to Solution
1150 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Noah_Coleman
6 - Interface Innovator
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
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
Ron_Daniel
8 - Airtable Astronomer
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?
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().

Noah_Coleman
6 - Interface Innovator
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!