Jun 20, 2022 02:17 PM
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 :
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.
Jun 21, 2022 12:26 AM
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?
Jun 21, 2022 12:39 AM
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.
Jun 21, 2022 12:41 AM
Sorry I’ve posted the wrong screenshot
Jun 21, 2022 01:27 AM
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:
Leave table woud look like this:
We would create the following fields for each month in the Team table
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.
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.
And once this is done the calculations should be correct once more
Jun 21, 2022 04:47 AM
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.
Jun 21, 2022 05:19 AM
Yeap that’s right!
Ha yeah, at least we can hide the ones we don’t need