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 150507 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(150507) Excel will evaluate that as 1557=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 150507 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:27B20635C81D4144834D8AC95D69722F(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 150507 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 