CODEWITHSUNDEEP
excelexcel-formula
Kim Sibthorpe
Kim Sibthorpe

Reputation: 1

Pro Rata holiday formula in Excel

I need to work out pro rata holiday using a start date, how do I do this? I have one column with the name and the column next to it with their start date. They are allowed 20 days pro rata holiday, but I need to work out what they are entitled to from their start date.

So for instance, if they started in September they wouldn't be entitled to the same amount of holiday as someone who started in January.

Upvotes: 0

Views: 586

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

The calculation would be (would need rounding): 

=Total_Holiday_Allowance - (Total_Holiday_Allowance/365)*Start_Date_Number

where Start_Date_Number is the day number in the year.

So with a holiday allowance of 20 days and a start date of today: 

=20-(20/365)*DATEDIF(DATEVALUE("1 January 2018"),TODAY(),"d")

This would give 3.068493 days holiday remaining (6th November) - round up or down is your choice.

Using cell references: 

=$B$1-($B$1/365)*DATEDIF($A$1,$B4,"d")

Where B1 is the holiday allowance, A1 is the start of the year date and B4 is the employee start date.
You may need to change 365 to account for leap years.

Upvotes: 1

Related Questions