calculating dates
 

=(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

calculating dates
 

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

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

MP wrote:

=(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

calculating dates
 

"MP" wrote:
=(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.

calculating dates
 

"Shawn O'Donnell" wrote in message
...
"MP" wrote:
=(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

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

calculating dates
 

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" wrote in message
...
Take a look at Chip Pearson's instructions on how to use =datedif().

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

MP wrote:

=(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

calculating dates
 

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.



MP wrote:

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" wrote in message
...
Take a look at Chip Pearson's instructions on how to use =datedif().

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

MP wrote:

=(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

--

Dave Peterson