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.

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

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


                )


            )


    )

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?

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

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?

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}

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

)))))))

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.

IF({Video Duration}<=60, "XXS",


...

Brilliant thank you Justin. Learning a new platform often takes learning these little things such as how they handle formulas and whatnot. Much appreciated.

DATETIME_DIFF(To, From, "seconds")

(Duration/3600) * {Rate/hr}

Gee, thanks for making this duration calc simple and easy to understand and implement. Well done.

DATETIME_DIFF(To, From, "seconds")

(Duration/3600) * {Rate/hr}

Curious on this one:


DATETIME_DIFF({End Date and TIme(Local)}, {Start date and Time(Local)}, “minutes”)


I changed the seconds to minutes but now I get:

I’d like it to be (hours) for the duration to show 2:00 based on this much shorter formula.


Also using all this, if let’s say the time is not an even, let’s say 2:00 hours, and maybe it goes from 7am-930am, would it show 2:30?

For now I could take away the formatting for now, so that it at least just shows 2.

Thanks!

@Sean_Lake1 You need to change minutes to seconds In your DATETIME_DIFF function.

Hi all, 

Hoping someone can help. I'm trying to create a time sheet where the user inputs the hours & minutes they worked in 15min increments - so 1.25 (1 hour 15 mins), 1.5 (1hr 30 mins) etc.

How do I get this to appear as 1hr 15 mins, 1hr 30 mins in an hh:mm format without asking them to do their time sheets in seconds and also have the ability to roll up the totals?

Ideally they would not be tracking start end times using date fields. Just a box yo input a decimal. 

Example: 

This is a platform I've used for timesheets in the past, Synergist

Thank you!