Hi everyone,
I’m currently working on a base for my HR using KasPer lite HT base (thanks for creating it). I’ve managed (ish) to calculate the working days minus public holidays for a specified month.
That said, I want now to calculate the number of working days - public holidays - employee leave for a specific month. For example, in June, you have 21 working days, employee 1 took 4 days off in June, so employee 1 worked 17 days… But I can’t figure out how to do it.
Employee leave are registered in a simple absence table with the following fields :
Employee
Start date
End Date
Leave year start
Leave year end
…
Sorry if it’s not cristal clear, I’m a beginner with Airtable, and I do not want to go back to Excel…
Thanks a lot in advance for your help.
Hi Adam,
Thanks a lot for your answer and for your email ! I will try to find time to call you but I’m a little bit in a rush at the moment.
Meanwhile, here’s a screenshot of my team table “équipe” in French.
As you can see, there’s no link to the month table.
Hi Audrey, thanks for the screenshots!
Hmm, if we want to have the Months table, we would have to have a column for each employee to display the number of days they worked for that month.
This is doable, but I don’t recommend it as it you would have to update it whenever employees joined or left, and if you have a lot of employees this would be troublesome to set up, and also makes the calculation more difficult.
Instead, I would suggest adding the months to the Team table, so that it looks like this:
And the Leave table woud look like this:
We would create the following fields for each month in the Team table
- A formula field to calculate the number of work days for that month
- A rollup field that will sum up the number of leaves taken for that month
- A formula field that will get the difference between the two previous fields, which will be the number of days the employee worked that month
This assumes you’re alright with changing the setup of your base though. Let me know if you do not want to change the setup and I’ll see what I can do
Regardless of which base set up we use, there’s a potential issue where the employee takes leave and it’s across two months, e.g.
- Start Date: 30 May
- End Date: 3 June
For this, I would suggest we make a formula field that will alert us of this issue, and if it occurs, we can manually delete that record and replace it with two records that are within each month, e.g.
- Record 1
- Start Date: 30 May
- End Date: 31 May
- Record 2
- Start Date: 1 June
- End Date: 3 June
And once this is done the calculations should be correct once more
