Spreadsheet: 812351d1673088025-help-with-formulas-for-entilement-days-and-service-holiday-tracker-example
Sheet: 2022

 | Holiday | H = Full Day Taken, H.5 = Half Day Taken
 | Duvet Days | D = Full Day Taken, D.5 = Half Day Taken
 | Lieu Days | L = Full Day Taken, L.5 = Half Day Taken |  |  | these codes are what are entered into the planner section to count and reduce the entiltement days
 | Unpaid | U =  Full Day Taken, U.5 = Half Day Taken
 | Sickness | S  =  Full Day Taken, S.5 = Half Day Taken
 | Compassionate | C =  Full Day Taken, C.5 = Half Day Taken
 | [NAME] | W  =  Full Day Taken, W.5 = Half Day Taken
 | Year | 2023 |  |  |  |  | used to calcate the mid year starters 1st years holiday, until the first full year starts, needs to be displayed in the holiday entitlement columns |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week |  |  |  |  |  |  | Week
 | First Name | Last Name | Department | Commenced Employment | commencement year plus 1 | Holiday Period Start | Years Service (Jan to Jan) | Days until the end of the year | Weeks until end of the year | Entitled days / 52 weeks | Days Holiday Due, First Year Only | Extra Days Granted | helper | Holiday Entitlement | Holiday Remaining | Duvet Day Entitlement | Duvet Day Remaining | Lieu Days Gained, from Weekend Working | Days In Lieu Taken | Sick Days Taken | Compassion Days Taken | Unpaid Days Taken | 2022-12-30 00:00:00
1 | bill |  | Design | 2001-01-02 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
2 | ted |  | Development | 2015-04-23 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
3 | [NAME] |  | Development | 2005-02-01 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2 |  |  |  |  |  |  |  | w
4 | jane |  | Development | 2012-03-12 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
5 | [NAME] |  | Development | 2004-07-28 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
6 | anne |  | Directors | 2011-06-13 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
7 | [NAME] |  | Directors | 2006-07-03 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
8 | bill |  | Finance | 2023-01-03 00:00:00 |  | 2023-01-01 00:00:00 |  |  |  |  |  |  |  |  |  | 2
9 | ted |  | Operations | 2012-08-01 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
10 | [NAME] |  | Operations | 2006-09-25 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
11 | jane |  | Projects | 2023-01-03 00:00:00 |  | 2023-01-01 00:00:00 |  |  |  |  |  |  |  |  |  | 2
12 | [NAME] |  | Projects | 2021-02-03 00:00:00 |  |  |  |  |  |  |  | 2 |  |  |  | 2
13 | anne |  | Projects | 2022-08-15 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
14 | [NAME] |  | Projects | 2021-09-20 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
15 | bill |  | Projects | 2023-01-03 00:00:00 |  | 2023-01-01 00:00:00 |  |  |  |  |  |  |  |  |  | 2
16 | ted |  | Projects | 2021-10-05 00:00:00 |  |  |  |  |  |  |  | 1 |  |  |  | 2
17 | [NAME] |  | Projects | 2022-03-09 00:00:00 |  |  |  |  |  |  |  | 1 |  |  |  | 2
18 | jane |  | Sales | 2005-02-01 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
19 | [NAME] |  | Sales | 2022-10-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
20 | anne |  | Sales | 2013-02-18 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 2
21 | [NAME] |  |  | 2023-08-28 00:00:00
22
 |  |  |  |  |  |  |  |  |  |  |  |  | =IF(H34<1,L34+M34,IF(H34<1,20+M34,IF(H34=1,21+M34,IF(H34=2,22+M34,IF(H34=3,23+M34,IF(H34=4,24+M34,IF(H34=5,25,IF(H34=6,25,IF(H34=7,25,IF(H34=8,25,IF(H34=9,25,IF(H34=10,26,IF(H34=11,26,IF(H34=12,26,IF(H34=13,26,IF(H34=14,26,IF(H34=15,26,IF(H34=16,26,IF(H34=17,26,IF(H34=18,26,IF(H34=19,26,IF(H34>=20,27,))))))))))))))))))))))