Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Calculating Time without using Date field type

Solved
Jump to Solution
1891 3
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!