calculating dates

[MP]

=(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12)

is this the easiest way to calculate number of years(including fractions)
between two dates?

I thought =YEAR(B7)-YEAR($B$6) would have done it
if b7 = 3/12/1999 and b6 = 1/12/1998 the answer is 1.00
so it's apparently just rounding the number to whole years

the first formula seems to work but I didn't think it would have to be that
complicated
thanks
Mark

 

[Dave Peterson]

Take a look at Chip Pearson's instructions on how to use =datedif().

http://www.cpearson.com/excel/datedif.htm

 

[Guest]

=(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12)

is this the easiest way to calculate number of years(including fractions)
between two dates?

I don't get 1.00--I get 1.16667

How accurate do you want these fractions of years to be? If you use MONTH
in the formula, you'll get only multiples of 1/12 of a year in the fraction
part.

If you use the day difference and divide by 365 (or 365.25), you'll get
closer.

=(B7-B6)/365.25

The difference of the dates gives you number of days. Make sure the result
cell is formatted to display a number, not a date.

 

[MP]

I don't get 1.00--I get 1.16667

Thats what I get with the formula
=(YEAR(B7)-YEAR($B$6))+((MONTH(B7)-MONTH($B$6))/12)

but with the formula
=(YEAR(B7)-YEAR($B$6))
I get 1.00

the result cell is format Number 2 places

I'll check the datediff function posted above

Thanks
Mark

 

[MP]

That too rounds to even number, not fractional
"y" Years The number of complete years between Date1 and Date2.

same as =Year(a2)-Year(a1)
Thanks though
Mark

I'm just surprised that it doesn't return the actual value and let the user
format as required.
Oh well, the one I came up with is good enough for what I need.
Just thought I must be missing something simple.

 

[Dave Peterson]

Did you see this formula on Chip's page?

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, "
& DATEDIF(A1,NOW(),"md") & " days"

You could use that like:

=DATEDIF(A1,NOW(),"y")+DATEDIF(A1,NOW(),"ym")/12

I stopped after months.