Prev: I think I need a multiple IF statement, or is there another wa
Next: Hide formula in list
From: DanWebster24 on 18 Mar 2010 08:51
In the Function Arguments dialog the official Formula Result is different
(and wrong) from the result shown after "=" just under the Serial number
field.
I am using the Year formula. Whatever formatting I use for the date neither
this formula nor Text to Columns can return the correct year.
EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial
number field it shows the correct result of = 2007
From: Mike H on 18 Mar 2010 09:06
Dan,

This question; at least to me, isn't clear. What are you entering and where
and what is the formula you are referring to?

If you enter something like
=YEAR(15-05-07)
Excel will evaluate that as 15-5-7=3 and come up with a year of 1900




--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"DanWebster24" wrote:

> In the Function Arguments dialog the official Formula Result is different
> (and wrong) from the result shown after "=" just under the Serial number
> field.
> I am using the Year formula. Whatever formatting I use for the date neither
> this formula nor Text to Columns can return the correct year.
> EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial
> number field it shows the correct result of = 2007
From: Fred Smith on 18 Mar 2010 12:03
Your explanation could have been a lot clearer. Here's my guess.

You are calculating =year(a1) where a1 contains the date May 15/2007.
You are formatting that cell as a date (with the format "yyyy"), and getting
the result 1905.

Your problem is that a year is not a date. Your year is 2007, but when you
format this as "yyyy", Excel thinks it's a serial number representing the
date Jun 29/1905. So it dutifully responds with 1905.

Your choices are:
1. Don't use =Year(...). Just format the date cell with "yyyy".
2. Use =Year(...), but format as a number

Regards,
Fred

"DanWebster24" <DanWebster24(a)discussions.microsoft.com> wrote in message
news:27B20635-C81D-4144-834D-8AC95D69722F(a)microsoft.com...
> In the Function Arguments dialog the official Formula Result is different
> (and wrong) from the result shown after "=" just under the Serial number
> field.
> I am using the Year formula. Whatever formatting I use for the date
> neither
> this formula nor Text to Columns can return the correct year.
> EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial
> number field it shows the correct result of = 2007

 | 
Pages: 1
Prev: I think I need a multiple IF statement, or is there another wa
Next: Hide formula in list