# Solved: Excel - Time Between Dates

Discussion in 'Business Applications' started by Donar, Jan 8, 2010.

Not open for further replies.
1. ### DonarThread Starter

Joined:
Jan 8, 2010
Messages:
4
Excel 2007
I want to calculate the time (in years) between 2 dates ... so that a 27 month span would appear as 2.25 years.
Using the formula " =year(A1)-year(A2) " yields an answer in whole years. I want an answer accurate to 1 (or 2) decimal places.
Defining the cell with the above formula in it, as a "Number" with 1 (or 2) decimal places does not do it,
Any suggestions ?
Thanks

2. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Try this

=(YEAR(A1)-YEAR(A2))+((MONTH(A1)-MONTH(A2))/12)

and use "Number" with 1 (or 2) decimal places

3. ### DonarThread Starter

Joined:
Jan 8, 2010
Messages:
4
Your formula worked ... gave me just what I needed.
Thank you
Donar

4. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You are very welcome.

5. ### edhicks

Joined:
Jun 30, 2008
Messages:
248
Another approach
b1=1/1/02 format as date
b2=3/1/04 format as date
b3 =(b2-b1)/365.24 format as number 2 decimal places

6. ### DonarThread Starter

Joined:
Jan 8, 2010
Messages:
4
My dates were 12/31/09 and 10/29/04 .... looking for answer (time between dates) in "Years".
.... using the "=(a2-a1)/365.24" formula .... I get an answer of "40073.17" ..... correct answer is "5.17"

Did I miss something ?
Thanks for the input

7. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Something not right there Donar

I have just used your formula =(a2-a1)/365.24 with the relevant days in A1 and A2 and I get 5.17

Open a blank worksheet and put the dates in and type the formula out again and see what you get (not forgetting to format the result cell with Value and 2 decimal places)

8. ### DonarThread Starter

Joined:
Jan 8, 2010
Messages:
4
The Villan & edhicks
My apolgies, i re-entered the values and the short formula ... and it does yield the correct answer. I don't know why my first attempt failed, I tried to re-create the "error" and could not.
Thank you both
Donar

9. ### edhicks

Joined:
Jun 30, 2008
Messages:
248
Donar
For what it's worth,
I was able to recreate the answer of "40073.17" with the formula
=a2-a1/365.24
Glad everything is working and WELCOME to this forum. Isn't it awesome.

10. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Forgot the brackets he he

Only kidding, but brackets do make a big big difference.

BODMAS springs to mind

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!