Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 08, 2020 10:34 AM
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
Jan 09, 2020 10:10 AM
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}
Jan 09, 2020 11:17 AM
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:
Jan 09, 2020 11:28 AM
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!
Jan 09, 2020 12:20 PM
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!
Aug 11, 2021 07:18 AM
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!
Aug 12, 2021 02:14 AM
How far have you got? If you share your base I’ll see if I can help!
Feb 16, 2022 06:07 AM
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?
Feb 17, 2022 05:23 AM
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
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!