Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
Enclose the date quotes:
=YEAR("3/7/2007") Otherwise you are saying Year( 3 divided by 7 divided by 2007) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "LadyCat" wrote in message ... I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
LadyCat wrote:
I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) Ladycat, Put the date in quotes? Beege |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
Excel is actually performing the division indicated by 3/7/2007 to return
the numeric value of: 0.000213538330130258 which, as a date/time value is roughly 18 seconds into 01-JAN-1900 To indicate to Excel that you want the value converted to a date, use this technique: =YEAR(--"3/7/2007") Excel will correctly interpret that you want to convert the text "3/7/2007" into the date represented. Does that help? *********** Regards, Ron XL2002, WinXP "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
TRY
=YEAR("3/7/2007") You are getting the year of the date represented by: 3 divided by 7 divided by 2007, or a very small decimal amount. "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
The other responders are correct in omitting the dbl-neg sign (--)....It's
not necessary because the YEAR function forces the conversion from text to date. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Excel is actually performing the division indicated by 3/7/2007 to return the numeric value of: 0.000213538330130258 which, as a date/time value is roughly 18 seconds into 01-JAN-1900 To indicate to Excel that you want the value converted to a date, use this technique: =YEAR(--"3/7/2007") Excel will correctly interpret that you want to convert the text "3/7/2007" into the date represented. Does that help? *********** Regards, Ron XL2002, WinXP "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
Isn't this whole question obviated by just entering the date in a cell and
referencing that cell in the formula? =YEAR(A1) works fine if A1 contain 3/7/2007. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Ron Coderre" wrote: The other responders are correct in omitting the dbl-neg sign (--)....It's not necessary because the YEAR function forces the conversion from text to date. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Excel is actually performing the division indicated by 3/7/2007 to return the numeric value of: 0.000213538330130258 which, as a date/time value is roughly 18 seconds into 01-JAN-1900 To indicate to Excel that you want the value converted to a date, use this technique: =YEAR(--"3/7/2007") Excel will correctly interpret that you want to convert the text "3/7/2007" into the date represented. Does that help? *********** Regards, Ron XL2002, WinXP "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
Probably: Yes.....provided that the referenced value in A1 is either a
bonafide date or text (with no leading spaces or other complications). I suspect that the post was an "exploratory question" and there may other underlying issues or intentions. *********** Regards, Ron XL2002, WinXP (BTW...."obviate" is one of my favorite words) "Dave F" wrote: Isn't this whole question obviated by just entering the date in a cell and referencing that cell in the formula? =YEAR(A1) works fine if A1 contain 3/7/2007. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Ron Coderre" wrote: The other responders are correct in omitting the dbl-neg sign (--)....It's not necessary because the YEAR function forces the conversion from text to date. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Excel is actually performing the division indicated by 3/7/2007 to return the numeric value of: 0.000213538330130258 which, as a date/time value is roughly 18 seconds into 01-JAN-1900 To indicate to Excel that you want the value converted to a date, use this technique: =YEAR(--"3/7/2007") Excel will correctly interpret that you want to convert the text "3/7/2007" into the date represented. Does that help? *********** Regards, Ron XL2002, WinXP "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?
Hi LadyCat,
It's quite obvious for me that you are trying to test the ability of the YEAR() function...since you already type the Year "2007" inside the Year function... if you type a formula =year(A1) , where A1 contains a date, then you indeed need to use a year function for some sort of YEAR series presentation... or by another way is... if its for presentation, then on A1, you can just format it "YYY" on the formatcellsnumbercustom and type "yyy" [without the quotes]. In this way you will not loose your real date value.. regards, driller -- ***** birds of the same feather flock together.. "LadyCat" wrote: I am expecting the answer as 2007 from the function of =YEAR(3/7/2007) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect Excel date 1900 is not a leap year 1/1/1901 < 367 | Excel Discussion (Misc queries) | |||
Dates prior to 1900 in Excel | Excel Discussion (Misc queries) | |||
FORMAT A CELL TO USE MO/DATE WITH NO YEAR:,DISPLAYS 1900? | Excel Discussion (Misc queries) | |||
1900 not a leap year | Excel Worksheet Functions | |||
is 1900 a Leap Year? | Excel Discussion (Misc queries) |