Holiday Accrual Formula

[meir0082]

Hi

I need to create a formula which will tell me the amount of accrued holiday employees have. My current spreadsheet is set up as follows:

C2 - Holiday Days Taken
D2 - Annual Entitlement
E2 - Holiday Year Start Date
F2 - Today's Date

If there's anything unnecessary or if I need any other info in there, please let me know!

Thanks so much!

 

[AlanY]

try this

Excel 2012
	C	D	E	F	G
1
2	Holiday Days Taken	Annual Entitlement	Holiday Year Start Date	Today's Date	Accrued Holiday
3	2	22	01/01/2016	19/04/2016	5.0
4
5
Sheet2

Cell Formulas
Range	Formula
G3	=ROUND((F3-E3)*D3/365,0.5)-C3

 

[meir0082]

Hi Alan

Thanks so much, can I ask you to explain the formula to me?

 

[AlanY]

(F3-E3) is the total days worked this year.

*D3/365 to work out the pro-rata of days entitled.

round() function to round it to 0.5 day (that can be changed to 1, or .25 as needed)

and minus days already taken (C2) gives the accured days.

hope that help

 

[meir0082]

Great help, thanks!

 

[AlanY]

you're welcome