Help

Re: Formula; Time worked?

1779 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Damon_Talbot
7 - App Architect
7 - App Architect

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?

37 Replies 37
Damon_Talbot
7 - App Architect
7 - App Architect

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

Gloria_Dasch
4 - Data Explorer
4 - Data Explorer

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:
233e78f6f3b874d3020a4994bcdc68a8c809acaf.png

Here’s the same formula with comments:
1d84da2dd92d1d06a050450c9feda216017f6fa5.png

Damon_Talbot
7 - App Architect
7 - App Architect

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

Derek_Clark
6 - Interface Innovator
6 - Interface Innovator

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:
Screenshot 2017-05-29 12.11.10.png

End Result:
Screenshot 2017-05-29 12.12.05.png

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.
    Screenshot 2017-05-29 10.45.25.png

  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.

Screenshot 2017-05-29 10.46.23.png

  1. 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.

Screenshot 2017-05-29 12.07.59.png
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.

Screenshot 2017-05-29 11.04.04.png

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

Best of luck!

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.

First off, this is exactly what I am looking for!!! Thank you so much!

However, when formatiing to a decimal with 1.00 format and adding another field after this formula with another formula, and using the next formula to multiply this outcome by 8.25, the outcome is not accurate. For example, “Hours Spent” would be 7.77 and when multiplied by 8.25, the outcome is 64.08 when the outcome should be 64.10. This formula does this with any Hours Spent value. Any suggestions?

Glad you found it useful. I am always frustrated by “how-to” posts that are difficult to follow so I do my best to lay things out clearly! Good luck with your projects!

Johan_Potgieter
4 - Data Explorer
4 - Data Explorer

does anyone know how to sum the durations together per date?

I want to use this to monitor the total time spend per day,
but the time is made up of various durations on various projects

Taking from Derek_Clark’s example above:
https://community.airtable.com/uploads/short-url/bGzkFYgsEVShXyrlOhdwrbcG0E2.png
I have something similar… On a next table, I would like to sum the durations for a specific date together
Lets say for May 7, 2017 there should return an answer of 7 hrs (3+3+0.5+0.5)

I created a roll-up on the table I want to sum the time, using the date (which correspond with dates time durations has been booked against). but that only seems to return the first row
This would be something similar to a sumif formula in excel.

any help as to what I am missing will be much appreciated

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 #”:
image.png

If you click on the area where it says “Sum”, you’ll see that you can use other formulas in those boxes too:
image.png

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:
image.png

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.

Thanks so much! That pushed me in the right direction

Sure!

If you want to group by Year, Month, and Day individually, like I said you can do, you’ll have to create a separate field for each - “Year”, “Month”, and “Day” - that contain formulas breaking out:

just the “Year” from the “Date” field,

DATETIME_FORMAT(Date, 'YYYY')

just the “Month” from the “Date” field,

DATETIME_FORMAT(Date, 'MMMM')

and just the “Day” from the “Date” field,

DATETIME_FORMAT(Date, 'D')

You can hide those fields, but then use them to group first on “Year”, then on “Month”, then on “Day”, and have a nice, nested, collapsible view.

Thank you this is exactly what I am looking for.

This is awesome! I converted it to populate days and hours instead of hours and minutes, but it’s off by about an hour? Here’s my current formula:

IF(
		{Completed Date/Time},
		 (DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'days') & " Days " & 
		 MOD( DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'hours'), 24) & " Hours "))

But as you can see, the time differences don’t quite add up properly.

Days%20%26%20Hours%20Spent%20Ex%202

Any suggestions?

Hi Derek,

Anyway I can use this formula and have it only give me total of hours worked on during working hours. If I have a project that started on 01/12/2020 at 10:00 and ended at 02/12/2020 at 11:00 it calculates hours total but is there a formula that only uses work hours to give you a total and takes away the hours not actually worked. So if there is only 7 hours of work in the day can it calculate between those hours?

Thank you for the help

Thanks so much, i just came across this post since i was looking for such a formula, and it worked

Thanks, this is so clean and simple!

and thanks for the explanation, i’m new to formulas and the explanations help me advance my understanding on how they work

Hey Farshad,

Sorry I didn’t notice your post earlier. I am not sure I understand your question. In any case, were you able to find a solution yet?