calculate annual vacation based on hiring and last working date

[Afarag]

Hello there,

please i need help in counting the number of annual vacation for an users through multiple years, based on the hiring date and the last working date for each user.
every use take 21 vacation days per year so per month it supposed to take 1.75 day.
so if an user hired in 1-jan-2015 and resigned in 1-Aug-2015 supposed to take 12.25 days *=7(months)*1.75

e.g: as per the picture i need to apply an equation that calculate the vacation through the period between the hiring and last working days, also put in consideration the month fractions.

gratefully,

 

[Chihiro]

See attached.

For 2011:
=IF([@[Last Working Date]]<DATE(2011,1,1),0,IF((DATE(2012,1,1)-MAX([@[Hiring Date]],DATE(2011,1,1)))<0,0,IF(DATE(2012,1,1)<[@[Last Working Date]],(DATE(2012,1,1)-MAX(DATE(2011,1,1),[@[Hiring Date]]))/(DATE(2012,1,1)-DATE(2011,1,1))*21,(MIN(DATE(2012,1,1),[@[Last Working Date]]+1)-MAX([@[Hiring Date]],DATE(2011,1,1)))/(DATE(2012,1,1)-DATE(2011,1,1))*21)))

Add 1 year to each of DATE() for 2012, so on so forth...

P.S. However, vacation entitlement calculations are usually bit more complex, as you need to consider workdays, pay period etc.

 

[Afarag]

hello Chihiro,

your calculation go well, thanks a lot for spent effort.

gratefully,