Count Years Between Two Dates

**sgwilliams** · 09-07-2011, 07:36 AM

I have been trying to create an employee data sheet that will show me the number of years an employee has worked for the company by subtracting the employees hire date from todays date. What I have tried so far does not work. not sure what I have done wrong.

todays date comes from feild F1 and looks like this "=TODAY()"

then I have the hire date feild C4 which is formatted to look like this "01/14/2008"

the years worked feild D4 is formated as a number and should look like this "3"

I have tried two formula's for D4 they are as follows:

1st: "=YEAR(F1)-YEAR(C4)" this is what it returns "1900"

2nd: "DATEIF(F1,C4,"y")" this returns "#NAME?"

any help would be greatly appreciated.

I have also noticed on other excel spreadsheets that when I open a document, the dates do not always refresh (ie. "TODAY()") is there a setting that will refresh a function when opening a file?

**Richard Buttrey** · 09-07-2011, 07:41 AM

Hi,

The first should be correct. Are you certain C4 is a date number?

Upload the workbook so that we can take a look

Regards

**arthurbr** · 09-07-2011, 07:50 AM

There is a typo in your second formula its is DATEDIF not DATEIF

**daddylonglegs** · 09-07-2011, 08:11 AM

Originally Posted by sgwilliams

"=YEAR(F1)-YEAR(C4)" this is what it returns "1900"

Are you sure D4 is formatted as number? - returning 1900 would suggest you have D4 custom formatted as yyyy

**sgwilliams** · 09-07-2011, 09:03 AM

Ok I figured it out, it was a couple of things. The mispelled funtion was part of the problem thank you arthurbr. The other problem was the order in which the dates was shown in the funtion. See example as follows:

=DATEDIF(C4,F1,"y") <-----correct order
=DATEDIF(F1,C4,"y") <-----incorrect order

hire date "C4" is 1/14/08
todays date "F1" is 9/7/11

Thanks for all the help fella's greatly appreciated!