Holiday Planner

[ZAINA_Internati]

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

[Olly_L]

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}

[ZAINA_Internati]

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:

[Olly_L]

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!

[ZAINA_Internati]

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!

[toni-macaroni]

Hi Olly,

I’m trying to do exactly the same here - I’m fairly new to Airtable and still getting to grips with formulas…

does your offer still stand to do a quick mock up? If so - that’d be really appreciated!

Thanks very much!

[Olly_L]

How far have you got? If you share your base I’ll see if I can help!

[Diana_Horqque]

Hello! I am actually in the process of starting one, but just saw this thread! Would it be possible to share yours with me please?

[Olly_L]

Hi, I will have to do a fair amount to remove everyone from mine.

To give you a rough idea I have:

Employees table
List of Employees and start dates plus any other info you want.

Employee Leave Summary table
This table has a Record (row) for each “Leave Period” for each Employee. So If someone’s holiday runs from January 21 to January 22 then this would have one Record and would be linked to the Employee.

It contains:

• Leave period start
• Leave period end
• Time worked (aka if they are part-time how much time they work)
• Annual Leave Pro-Rata
• Annual Leave (Calculated based on amount of year worked and part-time)
• Public holiday days
• Bookable leave (Leave available to be reserved)
• Leave arranged
• Leave still available

Leave
There would then be a record for each period booked off, linked to a “Employee Leave Summary” Record.

It would contain, the start date, end-date, number of days off and perhaps whether it’s been approved etc…

Let me know if you’d like further help!