Working days and employee leaves

[Audrey_Schmitt]

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.

[TheTimeSavingCo]

Hi Audrey, hmm, from your screenshot I don’t see any employee IDs or names, only the months. Could I get a screenshot of the table that contains the employee records and how it links to the months table you’ve shown here?

[Audrey_Schmitt]

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.

[Audrey_Schmitt]

Sorry I’ve posted the wrong screenshot

[TheTimeSavingCo]

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

1. A formula field to calculate the number of work days for that month
2. A rollup field that will sum up the number of leaves taken for that month
3. 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

[Audrey_Schmitt]

Thanks a lot for taking the time to work on this.
I guess I can change the way my base is working.

If I get it right, there will be all months of a given year in the “person” table, so twelve columns x 3 for calculation ? It seems to do a lot of columns but why not !

I hope I’ll have the time this week to try.

[TheTimeSavingCo]

Audrey_Schmitt:

If I get it right, there will be all months of a given year in the “person” table, so twelve columns x 3 for calculation ?

Yeap that’s right!

Audrey_Schmitt:

It seems to do a lot of columns but why not !

Ha yeah, at least we can hide the ones we don’t need