Formula; Time worked?

[Damon_Talbot]

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?

[mynetdude]

hey :slightly_smiling_face: 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

[Mics_Sky]

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 !

[mynetdude]

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.

[mynetdude]

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

[Mics_Sky]

humm! Can you copy/paste ?!

[mynetdude]

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

[mynetdude]

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

[Mics_Sky]

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”

,"")

[mynetdude]

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

Thanks again for your trouble :grinning_face_with_smiling_eyes:

[Blair_Quintana]

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.