Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Calculating # of Years in MS Excel

### #1 swicklund Posted 10 April 2006 - 10:48 AM

swicklund

Member

• Member
• 25 posts
I am having difficulty calculating the # of years (as an integer) between today and a date in the past. For example (today and A1 = 6/5/03).
If I use
=Year(Today()) - Year (A1)
then I get 3 as the answer. The actual value is between 2 and 3 so I want to get 2 as my answer.

I could also use:
=int((Today() - A1)/365)
but this does not take leap years into account.

Without writing a long and complicated formula, is there an easier way??
• 0

### #2 Octagonal Posted 10 April 2006 - 10:24 PM

Octagonal

Member 2k

• Member
• 2,528 posts
Hello swicklund,

In your formula YEAR() must have a cell reference inside the brackets. You can't have YEAR(TODAY()). So to work around this problem if you wish to work with todays date you would be to create what I call a dummy cell.

In cell BB1 enter =TODAY(), in cell BB2 enter =\$BB\$1 and hide the column (You can use whatever cells you wish as long as there is no chance of them being overwritten).

Now your formula to use would be =YEAR(\$BB\$2)-YEAR(\$A\$1)-IF(OR(MONTH(\$BB\$2)<MONTH(\$A\$1),AND(MONTH(\$BB\$2)=MONTH(\$A\$1),DAY(\$BB\$2)<DAY(\$A\$1))),1,0) assuming that \$A\$1 is the cell you wish to compare with.

This formula will create an integer for the differece between the two dates, and where the difference is not a full year the result would be 0.

HTH
Octagonal
• 0

### #3 Octagonal Posted 10 April 2006 - 11:29 PM

Octagonal

Member 2k

• Member
• 2,528 posts
swicklund,

Just checking a few sites and come across this little beauty.

=DATEDIF(startdate,enddate,"interval")

So your formula could be =DATEDIF(A1,TODAY(),"y") I tested in Excel and it works like a charm.

Looks like it would save a lot of hassles.

Octagonal
• 0

### #4 swicklund Posted 11 April 2006 - 06:08 AM

swicklund

Member

• Topic Starter
• Member
• 25 posts
Octagonal,

THANK YOU!!!! The [DATEDIF] function is perfect!!!!
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users