If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
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? |
#2
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
Penny32 wrote:
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) |
#3
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
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. -- David Biddulph "Penny32" wrote in message ... 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? |
#4
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
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" wrote: 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? |
#5
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
"Glenn" wrote: Penny32 wrote: 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) 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 |
#6
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
"Penny32" wrote: 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. |
#7
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
Penny32 wrote:
"Glenn" wrote: Penny32 wrote: 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) 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 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. |
#8
|
|||
|
|||
why does excel 2007 subtract 2009 from 2015 = 1900?
Penny32 wrote...
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. |
Thread Tools | |
Display Modes | |
|
|