Jump to content

Welcome to Geeks to Go - Register now for FREE

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
Photo

Calculating # of Years in MS Excel


  • Please log in to reply

#1
swicklund

swicklund

    Member

  • Member
  • PipPip
  • 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

Advertisements


#2
Octagonal

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 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

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 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.

Link: http://www.theexcela.../_t/t040303.htm

Looks like it would save a lot of hassles.


:whistling:
Octagonal
  • 0

#4
swicklund

swicklund

    Member

  • Topic Starter
  • Member
  • PipPip
  • 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

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP