Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 20, 2024 05:34 AM
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.
May 22, 2024 11:21 PM - edited May 24, 2024 09:17 PM
@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