Calculate Holiday Accruals Between Dates in Different Years

[thbutterflycollector_]

Hi,

I am trying to calculate the number of annual leave holidays accrued between two separate dates. I have already calculated the number of UK Public Holidays during the 3 year period and require also to factor in the number of leave days between the dates in 2010 and 2012 based on an annual entitlement of 27 days per year. I've had a look at another post at http://www.mrexcel.com/forum/excel-questions/498150-holiday-entitlement-calculator-formulas.html but the formula could not quite answer my question:-

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][D][/TD]
[TD="align: center"][E][/TD]
[TD="align: center"][F][/TD]
[TD="align: center"][G][/TD]
[/TR]
[TR]
[TD="align: center"]Start[/TD]
[TD="align: center"]Finish[/TD]
[TD="align: center"]Network Days (Less Public Holidays
'=NETWORKDAYS(D3,E3,$B$27:$B$52)[/TD]
[TD="align: center"]Annual Holidays Accrued
'=ROUND(F3*(27/(K3-J3)),2)[/TD]
[/TR]
[TR]
[TD="align: center"]01/01/2010[/TD]
[TD="align: center"]01/03/2012[/TD]
[TD="align: center"]315[/TD]
[TD="align: center"]7.77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

K3 = 01/01/2010
J3 = 31/12/2012
$B$27:$B$52 is a seperate table of all public UK holidays during 2010 to 2012

The current formula yields a result of 7.77 days which cannot be right as there are at least 27 days annual holiday accrued during 2011 and another 3 x 2.25?

Can anyone point me in the right direction where I might be going wrong?

Thanks and Kind Regards,

 

[AlanY]

Hi,

I am trying to calculate the number of annual leave holidays accrued between two separate dates. I have already calculated the number of UK Public Holidays during the 3 year period and require also to factor in the number of leave days between the dates in 2010 and 2012 based on an annual entitlement of 27 days per year. I've had a look at another post at http://www.mrexcel.com/forum/excel-questions/498150-holiday-entitlement-calculator-formulas.html but the formula could not quite answer my question:-

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][D][/TD]
[TD="align: center"][E][/TD]
[TD="align: center"][F][/TD]
[TD="align: center"][G][/TD]
[/TR]
[TR]
[TD="align: center"]Start[/TD]
[TD="align: center"]Finish[/TD]
[TD="align: center"]Network Days (Less Public Holidays
'=NETWORKDAYS(D3,E3,$B$27:$B$52)[/TD]
[TD="align: center"]Annual Holidays Accrued
'=ROUND(F3*(27/(K3-J3)),2)[/TD]
[/TR]
[TR]
[TD="align: center"]01/01/2010[/TD]
[TD="align: center"]01/03/2012[/TD]
[TD="align: center"]315[/TD]
[TD="align: center"]7.77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

K3 = 01/01/2010
J3 = 31/12/2012
$B$27:$B$52 is a seperate table of all public UK holidays during 2010 to 2012

The current formula yields a result of 7.77 days which cannot be right as there are at least 27 days annual holiday accrued during 2011 and another 3 x 2.25?

Can anyone point me in the right direction where I might be going wrong?

Thanks and Kind Regards,

try

Annual Holidays Accrued
=(J3-K3)/F3*27