How to sum up mothly working hours, holiday leave, sick leave and public holiday for every employee
Hi!
I have a system to record the working hours of employees. Through the form in table "timestamp", each employee reports the time of arrival and departure (and lunch break). Each entry is in a new line. Then, in table "Presence at work", I combined these rows for each employee to have all check-ins/check-outs for one day in one row. In this way, I then calculate lunch break and work time. If I group the rows by name and date, I can get a monthly attendance statement.
In a completely different table ("holidays leave"), vacation requests are submitted via a form, where vacation days are added up.
Now, in the table "Presence at work" (or in some extra table), I would like to have a monthly display for each employee of how many hours he worked, how many hours he used vacation and sick leave, and how many hours of holiday there were.
Any ideas on how to make it as easy and automated as possible? So that I don't have to connect the tables manually.
So how to add those missing information in the table here:
Or if you have any better solution?
I created a base copy (without automations) ad share it, if anyone is willing to take a look: https://airtable.com/invite/l?inviteId=invwcjzcY5ObD0Whc&inviteToken=0dd43c05bfe397698b168a8cb6b09db...
Thanks in advance.
@SpelaU wrote:Hi!
I have a system to record the working hours of employees. Through the form in table "timestamp", each employee reports the time of arrival and departure (and lunch break). Each entry is in a new line. Then, in table "Presence at work", I combined these rows for each employee to have all check-ins/check-outs for one day in one row. In this way, I then calculate lunch break and work time. If I group the rows by name and date, I can get a monthly attendance statement. compass mobile.dollar tree.com
In a completely different table ("holidays leave"), vacation requests are submitted via a form, where vacation days are added up.
Now, in the table "Presence at work" (or in some extra table), I would like to have a monthly display for each employee of how many hours he worked, how many hours he used vacation and sick leave, and how many hours of holiday there were.
Any ideas on how to make it as easy and automated as possible? So that I don't have to connect the tables manually.
So how to add those missing information in the table here:
Or if you have any better solution?
I created a base copy (without automations) ad share it, if anyone is willing to take a look: https://airtable.com/invite/l?inviteId=invwcjzcY5ObD0Whc&inviteToken=0dd43c05bfe397698b168a8cb6b09db...
Thanks in advance.
Hello, @SpelaU
To sum up monthly working hours, holiday leave, sick leave, and public holidays for each employee in Airtable, follow these steps:
Prepare "Presence at Work" Table:
Add fields for check-in, check-out, lunch break, and a formula for total daily hours:
sql
Copy code
DATETIME_DIFF({Check-out}, {Check-in}, 'hours') - {Lunch Break}
Link Tables:
Link "Holiday Leave" table to the "Presence at Work" table using a common field (e.g., employee name).
Create "Monthly Summary" Table:
Add fields for Employee Name, Month, Year, Total Working Hours, Total Holiday Leave, Total Sick Leave, and Public Holiday Hours.
Add Rollup Fields in "Monthly Summary":
Total Working Hours: Rollup Daily Hours from "Presence at Work".
Total Holiday Leave: Rollup Hours from "Holiday Leave".
Total Sick Leave: Rollup Hours from "Holiday Leave" filtered by leave type.
Public Holiday Hours: Formula field calculating hours based on public holidays.
Automate the Process:
Use Airtable Automations to update "Monthly Summary" when records are added/updated in "Presence at Work" or "Holiday Leave".
This setup automates the aggregation of monthly working hours, leave, and public holidays for each employee.
I hope my suggestion is helpful for you.
Best Regard,
angela683
