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.

Years of Employment	Vacation Days Per Year
Year One (1)	10		Hire Date January - March
Year One (1)	7		Hire Date April - June
Year One (1)	4		Hire Date July - September
Year One (1)	0		Hire Date October - December
Year Two (2)	10
Year Three (3)	11
Year Four (4)	12
Year Five (5)	13
Year Six (6)	14
Year Seven (7)	15
Year Eight (8)	16
Year Nine (9)	17
Year Ten (10)	18
Year Eleven (11)	19
Year Twelve (12) and Over	20 (Maximum)

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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