Sep 11, 2017 02:29 PM
This code is used in a “Duration” formula field in an Activity tracking database.
The formula calculates the hours and minutes of a duration of time (for an activity), and maintains a two digit display for minutes (H:MM) when the minutes portion of a duration is between 1 & 9 minutes. Without this code, a duration with a value for minutes between 1 & 9, for example a duration of 1 hour and 3 minutes, would display incorrectly as “1:3”, instead of correctly as “1:03”.
THE FIELDS IN THE CODE OF THE FORMULA ARE:
THE DURATION FORMULA FIELD CODE:
IF(OR({End Time}=BLANK(),{Date & Time}=BLANK()), “”,INT(DATETIME_DIFF({End Time},{Date & Time},‘m’)/60) & IF(MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)<10,":0"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60),":"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)))
CODE SECTIONS DOCUMENTATION:
IF(OR({End Time}=BLANK(),{Date & Time}=BLANK()), “”
EXPLANATION:
If the End Time field or the Date field are blank, prevent the display of the word “ERROR” that would be generated by the code that follows.
INT(DATETIME_DIFF({End Time},{Date & Time},‘m’)/60) &
EXPLANATION:
Return the hour portion of the duration calculation. This will be be concatenated with one of two versions of the minute portion of the duration in the code that follows. The minute potion will be preceded by a colon).
& IF(MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)<10,":0"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)
EXPLANATION:
If the minute portion of the duration is a value less than 10 (a single digit value), display the minute portion with a zero in tens place.
“:”&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)))
EXPLANATION:
Otherwise, the minute portion of the duration will be a value of at least 10 (a two digit value). Display that value.
Nov 27, 2017 05:54 PM
Thanks for an awesome Tutorial! I think it needs to be updated as it’s no longer working as a copy and paste.
You also can’t add together the outputted duration of a group of records, which is annoying.
Aug 02, 2018 12:20 AM
Change ‘m’ for “m”
IF(OR({End Time}=BLANK()),"",INT(DATETIME_DIFF({End Time},{Date & Time},“m”)/60)& IF(MOD(DATETIME_DIFF({End Time},{Date & Time},“m”),60)<10,":0"&MOD(DATETIME_DIFF({End Time},{Date & Time},“m”),60),":"&MOD(DATETIME_DIFF({End Time},{Date & Time},“m”),60)))
Aug 02, 2018 12:37 AM
Nope — the problem is that the forum software ‘pretty-prints’ quotes, turning straight quotes (''
) into curly ones ( ‘’ ) and straight double quotes (""
) into curly double quotes ( “” ). You can either replace the curly quotes with straight ones typed from the keyboard, or you can copy and paste the following, which preserves the straight quotes:
IF(
OR(
{End Time}=BLANK()
),
'',
INT(
DATETIME_DIFF(
{End Time},
{Date & Time},
'm'
)/60
)&IF(
MOD(
DATETIME_DIFF(
{End Time},
{Date & Time},
'm'
),
60
)<10,
':0'&MOD(
DATETIME_DIFF(
{End Time},
{Date & Time},
'm'
),
60
),
':'&MOD(
DATETIME_DIFF(
{End Time},
{Date & Time},
'm'
),
60
)
)
)
Dec 03, 2018 10:39 PM
Hi,
How would I go about creating a formula to calculate multiple entires of this formula, i.e. total number of hours worked over several dates?
Feb 04, 2019 11:34 AM
You can check the summary bar or ROLLUP them into another table.
Apr 16, 2019 12:59 PM
I’m trying to do something similar, but then I want to be able to multiple hours worked by cost rate. The problem with this formula is that it doesn’t format as a number so I can’t multiply by it, but I still like to see time displayed as H:MM
Is there a work around?
Apr 16, 2019 08:03 PM
I’m not sure if the complexity of the original formula is required, because time differences can be greatly simplified by using Airtable’s built-in duration formatting. For example, if you want to calculate the difference between two times, use this formula:
DATETIME_DIFF(To, From, "seconds")
Then set the formatting for the formula field to Duration, using the h:mm format.
Multiplying the duration by a given rate can be done by remembering that duration data is stored in terms of seconds. In the above screenshot, the formula in the {Total Cost}
field is:
(Duration/3600) * {Rate/hr}
Oct 22, 2019 12:04 PM
Hi there. I hope someone in this thread can help me. I have a duration column and in another column I want to run a formula that says, if the duration is under 1 minute then display “Short”, if the duration is over 1 minute then display “Medium” etc etc.
I keep getting an error, AirTable won’t accept my formula. I’m sure I’m missing something basic - can anyone help?
Here is my formula:
if({Video Duration}<=00:01:00,“XXS”,
if({Video Duration}<=00:04:00,“XS”,
if({Video Duration}<=00:10:00,“S”,
if({Video Duration}<=00:45:00,“M”,
if({Video Duration}<=01:00:00,“L”,
If({Video Duration}<=02:00:00,“XL”,
if({Video Duration}>02:00:00,“XXL”," "
)))))))
Oct 22, 2019 07:06 PM
Welcome to the community, @Brett_Snelgrove! :grinning_face_with_big_eyes: Airtable stores duration fields in seconds, so you’ll need to compare against whole numerical values, not a formatted representation of time. With that in mind, your first comparison should change to this:
IF({Video Duration}<=60, "XXS",
...
Make similar changes to the other time values, and it should work.