Vacation formula

[Steve]

HELP...I need to write a program that figures vacation days after an employee
hits 1 year, but prorated for the balance of that year, and then changing to
a calendar year fomula.

EX. Employee starts 6/1/06 - Employee has 1 year anniversary on 6/1/07.
He is given 1 day for each complete remaining month for 2007, so he will get
6 days (with a max of 10), and then on 1/1/08, he would get 10 more days.

Can anybody help with this??

 

[sb1920alk]

Assuming the start date is in A1, this will work:
=IF(YEAR(TODAY())-YEAR(A1)=1,MIN(10,12-MONTH(A1)),IF(YEAR(TODAY())=YEAR(A1),0,10))

 

[willy]

Learn how to use
NETWORKDAY
WEEKDAY
WORKDAY

These won't directly solve your particular problem but will go a long way to
making it easier. I found these a snap, no macros, set them up with a
secretary three years ago and she has only asked me back twice to explain it
to her. The fault is that she inserts new rows rather than adding on the
bottom of the list, this is not directly a problem with the functions above,
but with the way names are sorted alphabetically.

 

[Steve]

Thanks so much...this is going to work for me!!

Assuming the start date is in A1, this will work:
=IF(YEAR(TODAY())-YEAR(A1)=1,MIN(10,12-MONTH(A1)),IF(YEAR(TODAY())=YEAR(A1),0,10))