UK Pro-Rata Holiday Calculator

[Rob_010101]

Hello,

This one has the potential to be quite complicated, as UK holiday law is governed under the Working Time Directive.

I have been trying to make a calculator on Excel which calculates holiday entitlement and can also take into account if someone starts employment part-way through the holiday year - and their holiday must therefore be calculated pro-rata.

Note:

• Holiday year runs from 01/01 to 31/12
• Holiday entitlement is 20 days (plus BH) - 28 in total
• The law states that an employer cannot round down holiday. For example, if 10.24 days remain, this is rounded up to 10.5. Therefore the CIELING function has been used in the "remaining" balance to achieve this (as the company only allows booking in full days or half days).
• Remaining cell has bank holidays already taken as the Company policy is they are automatically used.
• Focus of this is the "full entitlement" cell (C7)

My calculator will show the correct figure of 28 days in the "full entitlement" cell (C7) when the holiday year start in cell C1 is 01/01/2023:

Template Holiday Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	NAME	Holiday year start	01/01/2023	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
2		Holiday year end	31/12/2023	January																																0.0
3		Full entitlement	28.00	February																																0.0
4		Hours worked per week	40.00	March																																0.0
5		Carry-over	0.00	April																																0.0
6		Bank holidays used	8.00	May																																0.0
7		Full entitlement	28.00000	June																																0.0
8				July																																0.0
9				August																																0.0
10		Remaining		September																																0.0
11		20.00	Days	October																																0.0
12				November																																0.0
13				December																																0.0
2023

Cell Formulas
Range	Formula
C7	C7	=SUM(SUM((C3/5)*C4)/52*SUM(C2-C1)/7)/8
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)
AJ2:AJ13	AJ2	=SUM(E2:AI2)

Cells with Data Validation
Cell	Allow	Criteria
E2:AI13	Whole number	between 0 and 1

Then when changing the "holiday year start" cell for someone who began employment part-way through the holiday year, the sheet should then return 16.5 in the "full entitlement" cell (C7) as per the UK gov calculation: Outcome - Calculate holiday entitlement - GOV.UK - but it returns the incorrect value of 14.46 (14.5)

Template Holiday Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	NAME	Holiday year start	26/06/2023	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
2		Holiday year end	31/12/2023	January																																0.0
3		Full entitlement	28.00	February																																0.0
4		Hours worked per week	40.00	March																																0.0
5		Carry-over	0.00	April																																0.0
6		Bank holidays used	3.00	May																																0.0
7		Full entitlement	14.46154	June																																0.0
8				July																																0.0
9				August																																0.0
10		Remaining		September																																0.0
11		11.50	Days	October																																0.0
12				November																																0.0
13				December																																0.0
2023

Cell Formulas
Range	Formula
C7	C7	=SUM(SUM((C3/5)*C4)/52*SUM(C2-C1)/7)/8
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)
AJ2:AJ13	AJ2	=SUM(E2:AI2)

Cells with Data Validation
Cell	Allow	Criteria
E2:AI13	Whole number	between 0 and 1

Never had this situation before. Is it even possible to calculate correctly?

Thanks,
Chris

 

[MARK858]

Maybe they are calculating from the start of the month (or to get 16.5 the last day of the previous month then applying a ceiling of 0.5)

Book1
	B	C
1	Holiday year start	26/06/2023
2	Holiday year end	31/12/2023
3	Full entitlement	28
4	Hours worked per week	40
5	Carry-over	0
6	Bank holidays used	3
7	Full entitlement	16.5
8
9
10	Remaining
11	13.5	Days
12
13
Sheet1

Cell Formulas
Range	Formula
C7	C7	=CEILING(SUM(SUM((C3/5)*C4)/52*SUM(C2-EOMONTH(C1,-1))/7)/8,0.5)
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)

 

[Rob_010101]

Maybe they are calculating from the start of the month (or to get 16.5 the last day of the previous month then applying a ceiling of 0.5)

Book1
	B	C
1	Holiday year start	26/06/2023
2	Holiday year end	31/12/2023
3	Full entitlement	28
4	Hours worked per week	40
5	Carry-over	0
6	Bank holidays used	3
7	Full entitlement	16.5
8
9
10	Remaining
11	13.5	Days
12
13
Sheet1

Cell Formulas
Range	Formula
C7	C7	=CEILING(SUM(SUM((C3/5)*C4)/52*SUM(C2-EOMONTH(C1,-1))/7)/8,0.5)
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)

Thanks for your reply,

I tried your formula with EOMONTH but it now results in an incorrect figure of 28.5 when starting on 01/01

Template Holiday Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	NAME	Holiday year start	01/01/2023	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
2		Holiday year end	31/12/2023	January																																0.0
3		Full entitlement	28.00	February																																0.0
4		Hours worked per week	40.00	March																																0.0
5		Carry-over	0.00	April																																0.0
6		Bank holidays used	8.00	May																																0.0
7		Full entitlement	28.50000	June																																0.0
8				July																																0.0
9				August																																0.0
10		Remaining		September																																0.0
11		20.50	Days	October																																0.0
12				November																																0.0
13				December																																0.0
2023

Cell Formulas
Range	Formula
C7	C7	=CEILING(SUM(SUM((C3/5)*C4)/52*SUM(C2-EOMONTH(C1,-1))/7)/8,0.5)
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)
AJ2:AJ13	AJ2	=SUM(E2:AI2)

Cells with Data Validation
Cell	Allow	Criteria
E2:AI13	Whole number	between 0 and 1

 

[MARK858]

Then try mround rather than ceiling for C7

 

[Rob_010101]

Then try mround rather than ceiling for C7

Works for 01/01

Template Holiday Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	NAME	Holiday year start	01/01/2023	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
2		Holiday year end	31/12/2023	January																																0.0
3		Full entitlement	28.00	February																																0.0
4		Hours worked per week	40.00	March																																0.0
5		Carry-over	0.00	April																																0.0
6		Bank holidays used	8.00	May																																0.0
7		Full entitlement	28.00000	June																																0.0
8				July																																0.0
9				August																																0.0
10		Remaining		September																																0.0
11		20.00	Days	October																																0.0
12				November																																0.0
13				December																																0.0
2023

Cell Formulas
Range	Formula
C7	C7	=MROUND(SUM(SUM((C3/5)*C4)/52*SUM(C2-EOMONTH(C1,-1))/7)/8, 1)
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)
AJ2:AJ13	AJ2	=SUM(E2:AI2)

Cells with Data Validation
Cell	Allow	Criteria
E2:AI13	Whole number	between 0 and 1

Not for 26/06 (should be 16.5)

Template Holiday Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	NAME	Holiday year start	26/06/2023	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total
2		Holiday year end	31/12/2023	January																																0.0
3		Full entitlement	28.00	February																																0.0
4		Hours worked per week	40.00	March																																0.0
5		Carry-over	0.00	April																																0.0
6		Bank holidays used	8.00	May																																0.0
7		Full entitlement	16.00000	June																																0.0
8				July																																0.0
9				August																																0.0
10		Remaining		September																																0.0
11		8.00	Days	October																																0.0
12				November																																0.0
13				December																																0.0
2023

Cell Formulas
Range	Formula
C7	C7	=MROUND(SUM(SUM((C3/5)*C4)/52*SUM(C2-EOMONTH(C1,-1))/7)/8, 1)
B11	B11	=CEILING(SUM(C7-C6)-SUM(E2:AI13), 0.5)
AJ2:AJ13	AJ2	=SUM(E2:AI2)

Cells with Data Validation
Cell	Allow	Criteria
E2:AI13	Whole number	between 0 and 1

 

[Rob_010101]

Then try mround rather than ceiling for C7

EDIT: I've tried 0.5 instead of 1 and it seems to be working. Will test this (finger's crossed).

 

[MARK858]

I didn't change the 0.5 to 1 when I did it, I just Changed Ceiling to MRound.
Whether it is accurate enough to work for all dates I haven't tested.