DATE

[Chris Lane]

Hi All
I live in South Africa, and I have an interesting problem that persists in
Excel 2003 and 2007.
=TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006
=month(now()) returns 12
=text(month(now()),"MMMM") returns January
=year(now()) returns 2006
=text(year(now()),"yyyy") returns 1905
Why would this be?
Thanks
Chris

 

[bobocat]

Hi Chris,

From your formula =text(month(now()),"MMMM") return January is correct.
=month(now()), will calculate the value of 12, when you turn to date format,
it will be 1900/1/12
the 12th day from 1900/1/1
then you change it to text format "mmmm", the result must be January.

 

[KC Rippstein]

When you use the TEXT function, you don't have to put "month" or "year" in
front of now. You already take care of that with the "MMMM" or "yyyy"
instructions. So just use =TEXT(NOW(),"MMMM") or =TEXT(NOW(),"yyyy")...keep
it simple to prevent accidental conversions that you are inadvertently
causing by adding unnecessary steps.