# Holiday Planner

Topic Labels: Formulas
3667 8
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

8 Replies 8
6 - Interface Innovator

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}

5 - Automation Enthusiast

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:

6 - Interface Innovator

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!

5 - Automation Enthusiast

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!

4 - Data Explorer

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!

6 - Interface Innovator

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

4 - Data Explorer

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?

6 - Interface Innovator

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!