Calculating and formatting a duration of time as H:MM

[Gary_Brocks]

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:

• Date & Time: A date field. The user enters the starting date and time of an activity.
• End Time: A date field. The user enters the ending date and time of an activity.
• Duration: The formula field. The the code of the formula will calculate the duration of the activity. The duration will be displayed in the format H:MM. If the duration is less than an hour, the H will display a zero. If the minutes are less than 10 (but greater than zero), the minutes ten’s place will display a zero. If the Date or End Time fields are blank the Duration field will be blank…

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.

[Jamie_Walker]

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.

[John_Vagg]

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

[W_Vann_Hall]

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

[tom_pandsmarine]

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?

[Elias_Gomez_Sai]

You can check the summary bar or ROLLUP them into another table.

[Susan_Lanier]

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?

[Justin_Barrett]

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}

[Brett_Snelgrove]

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”," "

)))))))

[Justin_Barrett]

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.