why does excel 2007 subtract 2009 from 2015 = 1900?

[Penny32]

have tried different formats for the dates, always comes up the same. have
formated in date/time format,
=(YEAR(10/16/2015)-YEAR(05/11/2009))*12+MONTH(10/16/2015)-MONTH(05/11/2009)=
1/0/1900.....what is wrong here?

 

[Glenn]

have tried different formats for the dates, always comes up the same. have
formated in date/time format,
=(YEAR(10/16/2015)-YEAR(05/11/2009))*12+MONTH(10/16/2015)-MONTH(05/11/2009)=
1/0/1900.....what is wrong here?

What you are saying is "Year of 10 divided by 16 divided by 2015" which is the
same as "Year of .00031017369)".

What you want is this:

YEAR(DATEVALUE("10/16/2015"))

Or, put the dates in other cells and then reference those cells. With
10/16/2015 in A1 and 5/11/2009 in B1:

=(YEAR(A1)-YEAR(B1))*12+MONTH(A1)-MONTH(B1)

 

[David Biddulph]

10 divided by 16 then divided by 2015 is a very small number, and as Excel
dates count from the beginning of 1900, the result of =YEAR(10/16/2015) will
be 1900. That presumably isn't what you want.
If you want 2015 you could, if you wanted, use =YEAR(DATE(2015,10,16)), or
of course you could just use =2015. If you put 10/16/2015 as a date into a
cell in Excel, assuming that your Windows Regional Settings use mm/dd/yyyy
as a date option, then =YEAR(cellref) would give 2015 as a result.

Note that even if you correct your formula, if you are working out a number
of months you would want to format the result as General or Number, not in
date/time format, or else a result of 23 months would display as 23rd
January 1900.

 

[Ron@Buy]

You have an incorrect syntax: To use your formula layout you need something
like:
=(YEAR(DATE(2015,10,16))-YEAR(DATE(2009,5,11)))*12+MONTH(DATE(2015,10,16))-MONTH(DATE(2009,5,11))
There are however easier ways to do this, I'm sure others will advise.

 

[Penny32]

What you are saying is "Year of 10 divided by 16 divided by 2015" which is the
same as "Year of .00031017369)".

What you want is this:

YEAR(DATEVALUE("10/16/2015"))

Or, put the dates in other cells and then reference those cells. With
10/16/2015 in A1 and 5/11/2009 in B1:

=(YEAR(A1)-YEAR(B1))*12+MONTH(A1)-MONTH(B1)
ok, did all that, but put the info into this as a date so u could see them. copied all the info from the excel help file. I used the formula exactly as written. Formmated the dates as =DATE(2009,4,28) as told. The date comes out in 1900's

 

[Penny32]

have tried different formats for the dates, always comes up the same. have
formated in date/time format,
=(YEAR(10/16/2015)-YEAR(05/11/2009))*12+MONTH(10/16/2015)-MONTH(05/11/2009)=
1/0/1900.....what is wrong here?

year format is for 1900-1999, how can i change this, how come this is like
this in excel 2007? also using vista.

 

[Glenn]

Your formula returns a number, specifically the number of months between the two
dates. Do not format the cell as a date.

Or, tell us what you are trying to accomplish.

 

[Harlan Grove]

have tried different formats for the dates, always comes up the same.  have
formated in date/time format,
=(YEAR(10/16/2015)-YEAR(05/11/2009))*12+MONTH(10/16/2015)-MONTH(05/11/2009)=
1/0/1900.....what is wrong here?

As others have already responded, without double quotes, Excel
evaluates 10/16/2015 as the arithmetic expression (10 divided by 16)
divided by 2015 = 0.000310173697270471, so YEAR(10/16/2015) = 1900.
Same for YEAR(5/11/2009). So (YEAR(10/16/2015)-YEAR(05/11/2009))*12 =
0. Likewise, MONTH(10/16/2015) and MONTH(05/11/2009) both equal 0. So
your formula returns 0, which when formatted as a date displays as
1/0/1900.

On my system, running Excel 2003, the formula

=(YEAR("10/16/2015")-YEAR("05/11/2009"))*12+MONTH("10/16/2015")-MONTH
("05/11/2009")

returns 77. FWIW, so does the formula

=DATEDIF("05/11/2009","10/16/2015","M")

and (less robust)

=INT(("10/16/2015"-"05/11/2009")/30.436875)

where 30.436875 = (97*366+303*365)/(400*12), the average number of
days in the standard 400 year Gregorian calendar cycle.