Eli asked in Computers & InternetSoftware · 9 years ago

how do i get to see people with 2 years anniversary? excel help?

hi, i have a list of people, employes and the date of them joining our company. i need to create another collum, where i will see when they reach 2 years with the company. anyone can help? thanks!

Relevance
• IXL@XL
Lv 7
9 years ago

Start date in A1

2 years later can be displayed using =EDATE(A1,24) format as date

If no display required until after 2 years passed use

=IF (NOW()>EDATE(A1,24), EDATE(A1,24),"")

• 9 years ago

=YEAR(NOW())-YEAR(A1)

This will show how many years an employee has been with the company. A1 is any cell which has the date that the employee started with the company. Just be sure the cell with the above formula is formatted to GENERAL. This may not be completely accurate if say the employee started in December of 2010 and today is January 2012. It will show that it has been 2 years when it has not yet been exactly 2 years.

If you want it a little more accurate, you can try this:

=IF(NOW()-A1>=365*2, YEAR(NOW())-YEAR(A1),"")

This is also slightly inaccurate since some years have 366 days. If you don't want it to show you anything but the 2 years, you can add an additional conditional into your IF function:

=IF(YEAR(NOW())-YEAR(A1)=2,YEAR(NOW())-YEAR(A1),"")

or

=IF(AND(NOW()-A1>=365*2,NOW()-A1<3*365), YEAR(NOW())-YEAR(A1),"")

I'm sure there's some complicated function you could write to make it even more accurate, but hopefully this helps.