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?

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.

Sorry I’ve posted the wrong screenshot

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

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.

Yeap that’s right!

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