This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Timesheets - Calculating total hours worked with e...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
432
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Aug 30, 2021 12:46 PM

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!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

Aug 31, 2021 08:40 AM

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!

6 Replies 6

Aug 30, 2021 12:54 PM

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.

Reply

Aug 30, 2021 01:53 PM

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.

Aug 30, 2021 05:07 PM

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.

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

Aug 31, 2021 08:29 AM

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

Solved
See Solution in Thread

Aug 31, 2021 08:40 AM

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!

Aug 31, 2021 08:42 AM