Vacation Due

[SilverM78]

I want to calculate when vacation is due,
1) Employee gets 40 hours after 6 months service.
2) 80 hours on Jan 1 following his first anniversary.
3) 120 hours on Jan 1 following his 7th anniversary

 

[Dick Kusleika]

Silver

Try this with the hire date in A1

=IF(NOW()>DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)),40,0)+MAX(0,INT((MIN(DATE(YEAR(
A1)+7,1,1),NOW())-DATE(YEAR(A1)+2,1,1))/365)*80)+MAX(0,INT((DATE(YEAR(NOW())
,1,1)-DATE(YEAR(A1)+7,1,1))/365)*120)