Skip to main content
Solved

Weekly overtime calculated automatically

  • June 11, 2025
  • 8 replies
  • 133 views
C
Forum|alt.badge.img+2

Hello all,

 

We have recently implemented Airtable on our team to account for hours worked by employees. To gather the information, I created a form in which they answer at the end of every work-day to take into account the actual hours worked. These answers feed a database that groups every reply by week/employee. In the database, I created a column of “Work-day” which is the employee’s weekly hours divided by 5 business days. This way, when they answer the form, I would have the daily overtime and then it would be grouped weekly and it would give me the weekly overtime.

However, the team is mainly composed by part-time workers and that’s where it becomes tricky. We don’t really enforce a fixed start/finish hour, so a person who has a contract of 18 hours/week, may work only 2 days one week and then 5 days the next week, picture below.

 

 

What I would like for it to do, and that I wasn’t able to, is to calculate how much overtime (or time they didn’t work) they are doing per week, based off of the form answers.

 

Thank you in advance for any help!

Best answer by TheTimeSavingCo

So if I understand correctly, I would be able to create an automation in which every time a new form is answered, a trigger happens and then a new line on the “WeekYear+People” page is created, right?

Yeap that’s right!  I’ve updated the base with the automation to do that:

 

And on a different note, would there be a way of cumulating these hours?

Using your example, Jerry would still need to work for 7 hours to reach his quota of the two weeks together. Instead of adding it by hand every time, is there a way of showing this in the database?

Hmm yeah, but this would depend on what kind of time period you’re looking at though.  If you’re okay with just looking at an overall figure that’d be easiest, you’d link each ‘WeekYear + People’ record to the right ‘People’ record and add a rollup:

 

8 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Does this look right?  I’ve created it here for you to check out

 

In the screenshot above, Jerry didn’t work for 12 hours on Week 24, and has 5 hours of overtime on Week 25

 

 

The idea is to create a new table where each record represents a single person for a single week here which allows us to create a roll up of all of the hours they worked.  Separately we've created a new field in People to log the person’s contract hours which we use for our calculation

Adam, Airtable Consultant - thetimesaving.company
BuildForAT
C
C
Forum|alt.badge.img+2
  • Ciro
  • Author
  • New Participant
  • June 12, 2025

Hi,

 

Thank you so much for taking the time and replying to my question!

 

So if I understand correctly, I would be able to create an automation in which every time a new form is answered, a trigger happens and then a new line on the “WeekYear+People” page is created, right?


And on a different note, would there be a way of cumulating these hours?

Using your example, Jerry would still need to work for 7 hours to reach his quota of the two weeks together. Instead of adding it by hand every time, is there a way of showing this in the database?

TheTimeSavingCo
Forum|alt.badge.img+31

So if I understand correctly, I would be able to create an automation in which every time a new form is answered, a trigger happens and then a new line on the “WeekYear+People” page is created, right?

Yeap that’s right!  I’ve updated the base with the automation to do that:

 

And on a different note, would there be a way of cumulating these hours?

Using your example, Jerry would still need to work for 7 hours to reach his quota of the two weeks together. Instead of adding it by hand every time, is there a way of showing this in the database?

Hmm yeah, but this would depend on what kind of time period you’re looking at though.  If you’re okay with just looking at an overall figure that’d be easiest, you’d link each ‘WeekYear + People’ record to the right ‘People’ record and add a rollup:

 

Adam, Airtable Consultant - thetimesaving.company
C
C
Forum|alt.badge.img+2
  • Ciro
  • Author
  • New Participant
  • June 13, 2025

That is just perfect!

 

It answers everything all my questions and hopefully it will be an automated view now.

Thank you so much again 😁

C
Forum|alt.badge.img+2
  • Ciro
  • Author
  • New Participant
  • June 15, 2025

Hi again,

 

So ever since you replied, I tried putting the examples you gave into my database and I’m having a hard time doing so. Since I can’t really see the formulas you used, I’m taking guesses and so far they haven’t been the right ones.

 

Two concepts that I can’t really grasp are:
1. Are you manually linking the Hour Log to each person every single entry? 
2. Whenever the week changes, do you have to manually create a new “weekline” in “WeekYear+People” page?

 

Here is a link to the database I have gathered from my side so far. And the work I have done before reaching out to the forum.

 

Thanks again in advance!

TheTimeSavingCo
Forum|alt.badge.img+31

re: 1. Are you manually linking the Hour Log to each person every single entry? 

Nope, we’d use an automation, similar to how we’re linking the WeekYear + People.  I’ve created the automation in the base for you to check out!


re: 2. Whenever the week changes, do you have to manually create a new “weekline” in “WeekYear+People” page?

Nope, that automation that links the Hour record to the WeekYear+People record automatically helps us to create the record.  If the record already exists it’ll just link instead

Adam, Airtable Consultant - thetimesaving.company
C
Forum|alt.badge.img+2
  • Ciro
  • Author
  • New Participant
  • June 19, 2025

Hi Adam,

 

Thank you again for your time and patience.

 

I have been banging my head trying to figure these automations out, but I just can’t wrap my mind around it.

The only thing missing, I think, for me to be able to import your example is understanding: what is the formula you used for the “WeekYear + People text”?

I’m having trouble linking everything together and catching the operation behind it. In other words, I understand what the automation is doing to create the new line, but I can’t understand the way it is done. 

TheTimeSavingCo
Forum|alt.badge.img+31

The formula for it is:

DATETIME_FORMAT(
  Date,
  'WW YY'
) & 
' - ' &
People

After you duplicate the base into your own workspace you’ll be able to see all the automations and formulas too

---

Hmm yeah, let me know if you have any questions?  Sorry, not too sure how to help!

Adam, Airtable Consultant - thetimesaving.company
Terms & ConditionsAccessibility statement