Holiday Planner

Hi there,

I would like to create a table to count and assign 25 holidays per year to employees.

I’ve managed to create a formula that sums holidays excluding national holidays and weekends but I would like to create a field called “Days Left” with a formula that calculates the holidays left out of a 25 days yearly allowance. That would mean I would have to calculate something like this:

25 (days) - (minus) Sum of Holidays taken up-to-date from the period 1st Jan 2020 to 31 Dec 2020

Any help would be much appreciated

Hi @ZAINA_International,

Presuming you have a field called Sum of Holidays Taken

You should be able to either have a formula with the 25 written in it.

25 - {Sum of Holidays Taken}

Or have a field with the holiday days allowed which would need to be in each record (row). So if that field was called Holiday Allowance, the formula would be:

{Holiday Allowance} - {Sum of Holidays Taken}

Thanks for your response.

I tried both but neither worked.

It does work for a single line / record, but won’t add up all holidays as employees take them.

See image below to illustrate:

Ah OK, yes summing a set of records within a single table is not possible in Airtable.

So what you will need to do is create a second table with a record for each employee, let’s call this table “Employee” table.

You would make your “By” field a linked field and link to the person from the Employee table.

Then in the Employee Table you could use a “Roll up” field referencing the “Sum of Holidays Taken” field to count all the days taken for each employee.

Let me know if you need more help and I’ll mock it up for you!

Thanks! That worked just fine, although I find it a bit fuzzy to create two tables for one function…

My last question would be if there is a way to have this formula renew the 25 days of holidays per year automatically. To demonstrate, I’ve added holidays spanning two different years and it keeps adding, so it messes up the function of the table.

Any ideas much appreciated. Thanks again!