Timesheets - Calculating total hours worked with empty fields

[Daniel_Burke]

Hi there, having a difficult time writing a formula, for an employee timesheet.

Currently, I am the using DATETIME_DIFF formula to calculate the total amount of hours worked within a 14-day period. I’m using two date/time fields for each day (ex: Day 1 - Start, Day 1 - End), so my formula is embarrassingly long:

DATETIME_DIFF({Day 1 - End}, {Day 1 - Start}, ‘hours’)+DATETIME_DIFF({Day 2 - End}, {DAY 2 - Start},‘hours’)+DATETIME_DIFF({Day 3 - End}, {Day 3 - Start},‘hours’)… and so on 11 more times.

My issue is, we have some employees that don’t work the full 14 days, so I need a formula that will calculate their total hours, and if some START/END fields are left blank, it will result as a “0” in the overall calculation.

I am a novice when it comes to formula writing, and have just started using Airtable this Summer. Any help or guidance is appreciated.

Thanks!

[Kamille_Parks]

Try the following pattern:

IF(
   AND({Day 1 - End}, {Day 1 - Start}),
   DATETIME_DIFF({Day 1 - End}, {Day 1 - Start}, 'hours'),
   0
) +
IF(
   AND({Day 2 - End}, {Day 2 - Start}),
   DATETIME_DIFF({Day 2 - End}, {Day 2 - Start}, 'hours'),
   0
) +
...

You can also consider using a second table where each row is a period worked. You’d have a link to the Employees table, two date fields, and a formula calculating the difference between the two dates. The Employee table would use a rollup field to sum up all the formula values in your new table.

[Daniel_Burke]

Thank you Kamille! That totally worked! I still have a lot to learn about Airtable formulas as well as just formulas in general. And, yes, i have been thinking about re-working the table and creating separate tables for each employee rather than records.

Question: Does the code have to cascade as it does in yours above? I noticed you can’t press return to shift down to the next line, unless I’m missing something? Copy and paste did it easily though.

[Kamille_Parks]

Daniel_Burke:

creating separate tables for each employee rather than records

I would not recommend separate tables for each employee. I would recommend a single table that lists each employee once (every employee is a record), and a table that has each time period for every employee as individual records.

Daniel_Burke:

Does the code have to cascade as it does in yours above?

Not if you’re typing directly within the formula editor, but as you’ve discovered you can paste in multi-line formulas.

[Daniel_Burke]

Thank you for the recommendations! I have one more hurdle I’m trying to jump over. I’m looking to calculate OT and double OT for our contractors. Our normal work day is 10 hours, any hours over that, with the cap at 12 is considered OT, anything else after 12 is considered double OT.

Currently, I have 14 fields (hidden), each with their total hour counts of each day. Running the double OT formula has been somewhat easier: IF({OT Hours - DAY 1}>12, {OT Hours - DAY 1}-12, {OT Hours - DAY 1}) …
But calculating OT hours which would be any hour >10 but not >12, so it’s just to calculate those two additional hours as OT. Does that make sense?

Appreciate all your help!

Daniel

[Daniel_Burke · Accepted solution]

Actually, I may have figured it out.

IF(AND({OT Hours - DAY 1} > 10, {OT Hours - DAY 1} < 13), {OT Hours - DAY 1}-10, IF({OT Hours - DAY 1} > 12, “2.0”))

However, the “2.0” is because I can’t format the number to a decimal (1.00 preferred). How would I write that?

Thanks!

[Daniel_Burke]

And now it seems the “2.0” allows for the decimal result. I think I solved it. :grinning_face_with_sweat: