Formula; Time worked?

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

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

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

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 !

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

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

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

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.

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”

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

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 !

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.

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

humm! Can you copy/paste ?!

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

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

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”

,"")

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:

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.

I haver not been able to get MOD to work to give me hours broken down to fractions. I also have another question;

Is there a way to compute the time from “Check In” to the current time? I would like that value to show instead of “0” when “Check Out” is empty

Did anyone get this to work? Using the code that’s listed, it doesn’t really calculate the time spent on each project.

I didn’t want seconds either, but since it didn’t work for me. What might I be doing wrong?

Thanks

Here’s a working formula for showing hours/minutes between two date fields OR between one date field and NOW().

IF(
	AND(NOT({checked in}), NOT({checked out})),
	"Nothing to do",
	IF(
		{checked out},
		ROUND( DATETIME_DIFF({checked out}, {checked in}, 'hours'), 0 ) & " hrs " & 
		ROUND( MOD( DATETIME_DIFF({checked out}, {checked in}, 'minutes'), 60 ), 0 ) & " min"
		,
		ROUND( DATETIME_DIFF( NOW(), {checked in}, 'hours'), 0) & " hrs " &
		ROUND( MOD( DATETIME_DIFF( NOW(), {checked in}, 'minutes'), 60), 0) & " min"
	)
)

Here’s what it outputs:

Here’s the same formula with comments:

Thanks!1 That is working quite well with a couple modifications to fit my needs

My time clock solution

This is another straightforward formula that will display number of hours worked as a decimal:

IF({Stop Time},DATETIME_DIFF({Stop Time},{Start Time},"")/3600)

Example:

End Result:

I like this short formula because it automatically gives you the number of hours between two DATE_TIME fields as a decimal instead of the number of hours and minutes. (“1.5” vs “1hrs 30min”)
This is convenient if you are tracking hours worked because you can easily multiply the decimal by the employee’s hourly rate.

How this formula works:

1. By beginning with an IF statement the formula only triggers if the “Stop Time” field is filled. This prevents your table from becoming cluttered with error messages or zeros.
   

2. The DATETIME_DIFF function returns the difference between datetimes in specified units. By default, it will return the number of seconds–which works great for our needs.
   NOTE: be sure that you enter the {Stop Time} BEFORE the {Start Time} or else the time difference will be displayed as a negative number.

4. Now we just divide the number of seconds by 3600 (the number of seconds in an hour) to get the number of hours between our two datetimes.
   NOTE: be sure that you do not include a comma when writing 3600 or else you will throw off the decimal place in your final number.

5) Finally, change the fields format from an “integer” to a “decimal”. You can then set how precise you would like your calculations to be by choosing the number of decimal places you would like your final number to include. For the purpose of a time clock, I find that the single decimal place works well.

Let me know if you find this helpful or if you notice any potential problems with this solution.

Best of luck!

IF({Stop Time},DATETIME_DIFF({Stop Time},{Start Time},"")/3600)

This formula is both elegant and effective. It works! Thank you for the detailed explanation with of the formula. I’m definitely a newbie in this universe and grateful to the largess of those with greater knowledge.