Vacation accrual formula

[lemfil]

I have been tasked with developing a spreadsheet to track employee vacation accrual. Employees begin accruing andare able to use vacation days once they have been with the company 90 days from their start date. After the calendar year of your hire date has been completed, your vacation willaccrue monthly on a pro-rata basis beginning January 31 (i.e. 0.83 days per month in Year 2).You will start Our vacation policy is as follows.

[TABLE="width: 564"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Years of Employment [/TD]
[TD]Vacation Days Per Year[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year One (1) [/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Hire Date January - March[/TD]
[/TR]
[TR]
[TD]Year One (1) [/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Hire Date April - June[/TD]
[/TR]
[TR]
[TD]Year One (1) [/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Hire Date July - September[/TD]
[/TR]
[TR]
[TD]Year One (1) [/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Hire Date October - December [/TD]
[/TR]
[TR]
[TD]Year Two (2) [/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Three (3) [/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Four (4) [/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Five (5) [/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Six (6) [/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Seven (7) [/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Eight (8) [/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Nine (9) [/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Ten (10) [/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Eleven (11) [/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year Twelve (12) and Over[/TD]
[TD]20 (Maximum) [/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


So far I have the following information set up:
A2 - Name
B2 - Start date
C2-N2 - number of days used broken down by month
O2 - Total number of vacation days used this year
P2 - Balance remaining (where I need to input my formula)


I would really appreciate any help anyone can give me.

Thank you in advance.

 

[Anand Sharma]

To calculate Total Number of Try this

=INT(IF(NOW()-A9>90,(NOW()-A9)/30,0))

Balance vacation =INT(
IF(NOW()-A9>90,(NOW()-A9)/30,0)) - number of leaves Taken