Calculate Year Correctly

[Guest]

I am developing a spreadsheet to help me with employee vacation tracking. I have varying vacation amounts, depending on the number of years of service. To help calculate what vacation is due, I put their hire date in and in another cell I have today's date. The problem is, when I tell it to calculate by the year, it is wrong because the (Year(F2)-Year(F1)) type formula looks ONLY at the year, not the whole date. Therefore, an employee beginning on 12/31/1999 would have 1 year of service on 01/01/2000, according to this formula. I can't figure out how to account for the enire 360 days before it awards vacation. Any suggestions?

 

[Frank Kabel]

Hi
=DATEDIF(F1,F2,"y")

see:
http://www.cpearson.com/excel/datedif.htm

 

[Rob]

We have a similar issue here I used a formula in one cell that referenced a
named cell called "HireDate" I added 5 to the year listed in HireDate

=DATE((YEAR(HireDate)+5),MONTH(HireDate),DAY(HireDate))

This value would indicate when that employee would reach his 5-year
anniversary. You could change this to account for any number of years.

HTH
Robert

I am developing a spreadsheet to help me with employee vacation tracking.

I have varying vacation amounts, depending on the number of years of
service. To help calculate what vacation is due, I put their hire date in
and in another cell I have today's date. The problem is, when I tell it to
calculate by the year, it is wrong because the (Year(F2)-Year(F1)) type
formula looks ONLY at the year, not the whole date. Therefore, an employee
beginning on 12/31/1999 would have 1 year of service on 01/01/2000,
according to this formula. I can't figure out how to account for the enire
360 days before it awards vacation. Any suggestions?