Vacation Due

[SilverM78]

Thank You Mr. Kusleika For your reply

A1 = 03/24/2004
Formula in B1
=IF(NOW()>DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)),40,0)+MAX(0,IN
((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)
Result = 40 This was result I was looking for but when I changed th
date i did not get result I was looking for.


A2 = 3/24/2002
Formula In A2
=IF(NOW()>DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),40,0)+MAX(0,INT((MIN(DATE(YEAR(A2)+7,1,1),NOW())-DATE(YEAR(A2)+2,1,1))/365)*80)+MAX(0,INT((DATE(YEAR(NOW()),1,1)-DATE(YEAR(A2)+7,1,1))/365)*120)
Result = 40 I was looking for 80

A3 = 3/24/1996
Formula in A3
=IF(NOW()>DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)),40,0)+MAX(0,INT((MIN(DATE(YEAR(A3)+7,1,1),NOW())-DATE(YEAR(A3)+2,1,1))/365)*80)+MAX(0,INT((DATE(YEAR(NOW()),1,1)-DATE(YEAR(A3)+7,1,1))/365)*120)
Result = 560 I was looking for 12

 

[duane]

with start date in cell a1, and today() in cell a2

=if(a2>=DATE(YEAR($A$1)+8,1,1),120,if(a2>=DATE(YEAR($A$1)+2,1,1),80,if(a2>=(IF(MONTH($A$1)<7,(DATE(YEAR($A$1),MONTH($A$1)+6),
(DATE(YEAR($A$1)+1,MONTH($A$1)-6,DAY($A$1)))),40,0)))

first test for 7 yrs, then 1 yr, then 6 month