Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default year formula returning incorrect value

As I understand it, the YEAR formula should return the year to which the
formula relates. I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. Can someone tell me what I'm
missing?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: year formula returning incorrect value

It sounds like the issue might be with the formatting of the cell containing the date. Here's what you can try:
  1. Select the cell containing the date (B2 in this case).
  2. Right-click on the cell and select "Format Cells".
  3. In the "Number" tab, select "Date" as the category.
  4. Choose the desired date format (e.g. "4/24/2008").
  5. Click "OK" to save the changes.

Once you've formatted the cell correctly, try the
Code:
YEAR
formula again in cell A2. It should now return the correct year (2008 in this case).

If the issue persists, it's possible that there's an issue with the date itself. Double-check that the date is entered correctly and that it's not a text string (which can sometimes cause issues with formulas). You can also try using the
Code:
DATEVALUE
function to convert the text string to a date value before using the
Code:
YEAR
formula. For example, "=
Code:
YEAR(DATEVALUE(B2))
".
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default year formula returning incorrect value

The serial number for this particular date is


39562


if you change the format from date to general what do you get?

For year 1905 the serial number would be between 1828 and 2192


serial number is the number of days since Jan 0 1900 given that Excel AD
starts then (at least for Windows)

--


Regards,


Peo Sjoblom

"dbdewitt" wrote in message
...
As I understand it, the YEAR formula should return the year to which the
formula relates. I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. Can someone tell me what
I'm
missing?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default year formula returning incorrect value

Hi,

=Year(B2) would return 2008 if B2 contained a correctly formatted 2008
date. If it wasn't correctly formatted then I'd expect it to return either
1900 or 1904 depending on which date system you had set in your options. If
it's returning 1905 then that's odd and suggests B2 has a value in the range
of 1828 - 2192 in it, are you sure that's correct and not a typo?

Mike

"dbdewitt" wrote:

As I understand it, the YEAR formula should return the year to which the
formula relates. I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. Can someone tell me what I'm
missing?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default year formula returning incorrect value

(Found it)...The format of the cell where the formula is (in this case A2),
needs to be something other than a date (i.e. general or number).

"dbdewitt" wrote:

As I understand it, the YEAR formula should return the year to which the
formula relates. I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. Can someone tell me what I'm
missing?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default year formula returning incorrect value

Your problem is that you forgot that YEAR returns the 2008 as a number, not
as an Excel date. You should have the cell formatted as general or number,
but instead you formatted the cell as a date in yyyy format. The number
2008 would be Excel's representation of a date 2008 days on from the
beginning of January 1900. 2008 days on gets you to 30th June 1905, and you
have asked it to display that number in yyyy format.

If all you wanted to do was to display 4/24/2008 in yyyy format, you didn't
need the YEAR() function, you could merely have formatted the cell (B2
itself, or a cell containing the formula =B2) to display that way.

Either use =YEAR() and format as General or Number
Or use formatting as yyyy
Or use =TEXT(B2,"yyyy") to return 2008 as text
Or use =--TEXT(B2,"yyyy") as another way to return 2008 as a number

If you try to mix and match without remembering whether your cell contains a
number or an Excel date, you can readily get confused, as you found out.
--
David Biddulph

"dbdewitt" wrote in message
...
As I understand it, the YEAR formula should return the year to which the
formula relates. I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. Can someone tell me what
I'm
missing?



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 747
Default year formula returning incorrect value

I guess the date is fine but the cell A2 is been formatted as "yyyy"
for this reason it is giving 1905 instead of 2008.
change to general category ....



On Sep 25, 2:06*pm, dbdewitt
wrote:
As I understand it, the YEAR formula should return the year to which the
formula relates. *I have a date of 4/24/2008 in a cell (let's say it's in
cell B2), when I enter the formula (let's say in cell A2) "=YEAR(B2)", the
result is 1905 where the result should be 2008. *Can someone tell me what I'm
missing?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup returning incorrect value Sherry Excel Discussion (Misc queries) 3 January 29th 08 08:57 PM
Lookup returning incorrect result btrotter Excel Worksheet Functions 1 June 7th 07 03:17 PM
IF function returning incorrect result Hillary E. Excel Worksheet Functions 2 August 22nd 06 05:43 PM
formula returning incorrect blanks [email protected] Excel Worksheet Functions 6 March 9th 06 08:17 PM
formula returning incorrect blanks [email protected] Excel Discussion (Misc queries) 7 March 9th 06 08:15 PM


All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"