# Automatically calculate overtime?

2768 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi there,

We recently decided to use Airtable as our payroll software, everything is fine but there's one problem and it's about the automatic calculation of overtime per week.

We found a way to calculate those hours but it involves manually copying the sum of all the hours worked per week in the "Total heures travaillées" (total hours worked) column, we find this number of hours in the "sum" of the column "Durée" (duration). Thus, copying this sum in the column "Total heures travaillées" (total hours worked) allows us to see the total of overtime hours per week in the other column "Heures supplémentaires" (Overtime hours).

In conclusion, we have to manually copy all the total hours worked per week to have the number of overtime hours so I wanted to know if there's a way that overtime can be calculated automatically without having to do anything and that we can delete the column "Total heures travaillées" (total hours worked)?

For your information, in France employees can work a maximum of 35 hours per week, so working more than 35 hours = overtime, hence the "35" which appears by default in the column "Total heure semaine" (total weekly hours).

You will find all our formulas attached.

Thank you and I hope you understood, it's a bit hard to explain especially since English is not my first language!

Laurie

4 Replies 4
6 - Interface Innovator

I would create another table called weeks. Create one record for each week. Link each day to the respective week. You can do the linking with Automations. Add a rollup field in the weeks table that sums the linked durations. Add a lookup field to this summation in the table that contains the hours worked. you should be able to figure it out from there.

Matthew's solution will solve your problem.  Once you've got that working, you could also create a formula field in the table with the formula `WEEKNUM({Jour}) & " " & YEAR({Jour})`, and then paste the value of that field into a linked field to the `Weeks` table, which would automatically create or link to the right records in the `Weeks` table for you

(We include YEAR() in the formula above to ensure that next year the records will be created / linked properly)

5 - Automation Enthusiast