Formula; Time worked?


#1

I am looking for a formula that can be used to determine the time worked. I have a table that is a “Check In / Out” which includes two fields in of Check in and Check out. Is there a formula that can be set in another field that will give the total time?

Bonus, is there a way to add ALL of the times from that table?


#2

Try DATETIME_DIFF(End, Start, ‘minutes’) between date fields, check time option.
The total(SUM) is already made by Airtable at the bottom line of the Airtable window.

Hope it helps


#3

Thanks! That seems to work. Is there a way to prevent an error when someone has not “checked out” yet? Since the check out field is blank until they would be done it gives an error in that entry and the SUM


#4

Just looked a bit closer and its not computing the time correctly :frowning:


#5

It works for me with ‘Hours’ or ‘Minutes’.
Can you check the fields format, just in case !

To prevent from calculating an empty field, you can insert a logical test:

IF({checked out},
DATETIME_DIFF({checked out}, {checked in} , ‘hours’), 0)

In case it doesn’t work feel free to show your calc !


#6

Thanks for trying to help. It is still computing the times wrong.


#7

In your date fields,
can you stick " Use the same time zone (GMT) for all collaborators "
and see if it 's better !
It must be On or OFF for the 2 date fields


#8

That was the issue :slight_smile: Changed them bit to Off and works perfect. thanks!!


#9

I was able to make use of this for my own purposes:

IF({checked out},
DATETIME_DIFF({checked out}, {checked in} , ‘hours’), 0) but I would like to also have the minutes not just the hour so right now if I go from 1:30 to 2:50 that should be 1hr 20mins (or 1.2?) but doing so only shows it in 1.0 and no minutes are considered.


#10

Hi Shannon_Mason,
I did something close to your calculation.
I used DATETIME_DIFF and MOD function to get minutes and seconds.

Here is my calc, you can try it with your own fields in place:

((((DATETIME_DIFF(TODAY(), {Date max}, ‘hours’) & "hrs, ") &
MOD(DATETIME_DIFF(TODAY(), {Date max}, ‘hours’), 60)) & " mins, “) &
MOD(DATETIME_DIFF(TODAY(), {Date max}, ‘minutes’), 60)) & " secs”


#11

hey :slight_smile: thanks for the quick reply,

the formula you posted only calculates one field instead of two? Also it doesn’t have “IF” anymore, which is needed to prevent it from calculating an empty field


#12

Absolutely wright ! The only goal was to point out a way to have hours, minutes and seconds !

In my case, I needed only 1 field and TODAY() function. You can replace TODAY() by your second field.

The same for the IF() , you have to keep your calculation and integrate the MOD() function.

So, here is a quick copy/paste :

IF({checked out},

((((DATETIME_DIFF({checked out}, {checked in} , ‘hours’) & "hrs, ") &
MOD(DATETIME_DIFF({checked out}, {checked in} , ‘hours’), 60)) & " mins, “) &
MOD(DATETIME_DIFF({checked out}, {checked in} , ‘minutes’), 60)) & " secs”

,"")

Hope it helps !


#13

oh that was too easy… I didn’t see that the parenthesis extended all the way to the end of the line, I coulda figured that part out other than the IF and MOD parts… this is more programming language than an excel formula that I am used to so not trying to make somebody else figure it out for me though I do see that when creating a formula field you can select a variety of functions to use but for a layperson like me this is a bit more complex since there’s no SUM(A1-B20) like you would do in Excel and it would be to Airtable’s benefit to make it easier IMHO

But now that I’ve seen how you did it this part is fairly easy I hope I won’t need more code for awhile.

Edited to add additional details since adding the sniplet:

This isn’t hugely important but its kinda pointless but I see a “=” in front of 1hr so it looks something like this “-1hr 25mins 30secs” need to remove the - somehow.


#14

wished I knew programming a bit better; but I’m having trouble with - being there and now its showing a minute off


#15

humm! Can you copy/paste ?!


#16

I’ll send you a link to a screenshot and show you what the code does :slight_smile: (better than copy/paste)


#17

ok here it is

you can see its from 11am to 12pm it is 1hr and 1 min it should be 1hr 0 min

I don’t really need the seconds though but I’ll just leave it for simplicity

https://cloudup.com/c6Ch9ZtoGE3/f <— that’s the screenshot


#18

The minus sign is probably due to the order in witch you provide dates fields in the Datetime_Diff().
Try starting by the “in” and then “out”

IF({checked out},

((((DATETIME_DIFF({checked in},{checked out} , ‘hours’) & "hrs, ") &
MOD(DATETIME_DIFF({checked in},{checked out} , ‘hours’), 60)) & " mins, “) &
MOD(DATETIME_DIFF({checked in},{checked out} , ‘minutes’), 60)) & " secs”

,"")


#19

oh that’s an easy fix… just change the order! :smiley:

Thanks again for your trouble :smile:


#20

Is it possible for you to show the field type settings in your shared table (https://airtable.com/shrs4VJkVI8LJQGrt) ? I guess make it editable would work. I learn so much better by seeing the everything working in action and what each field types are… Kinda like jfiddle examples.