Re: Formula; Time worked?
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:
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:
-
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.
-
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.
- 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 you create a view in the Time Clock table and Group it by the “Date” field, you can see Sums per day, similar to how I have mine grouped by “Sample #” and I can see sums of “Lab Data” per “Sample #”:
If you click on the area where it says “Sum”, you’ll see that you can use other formulas in those boxes too:
And the formula will apply to however you group the records - even sub-groups within a group will show their own totals, so you could group by year, then month, then day, and see “Sum” for each one, similar to how I’ve done here, grouping by “Sample Round” then by “Test Part” - each “Test Part” has it’s own Stdev calc’d, but then the whole sample round also has Stdev calc’d on it:
You can not act on or port out this data in any way though - you can only view it - so if that’s what is needed, this will not suit your needs.
