Skip to main content

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

 

 

your english is great.

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. 

your english is great.

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)

your english is great.

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. 


Thank you very much for your answer.

Btw can you tell me how to do the automation to link each day to the respective week please...?

Thanks!!!

Thank you very much for your answer.

Btw can you tell me how to do the automation to link each day to the respective week please...?

Thanks!!!


Basically you could create an automation that is triggered when there is a new record in the days table. The automation would use the 'find records' action and the 'update record' action. 'find records' would find the Week record based on the date of the day. the 'update record' step would then update the field in the Days table that is linked to Weeks and update it with the record ID found in the previous step.

Reply


Terms & Conditions