TEXT Function Troubles

[Sarah]

Hello
I'm in Australia where dates are written dd/mm/yy (so
July 16th is written 16/07/04) and Excel is set up to
format this way. I'm having a very strange problem where
a date reference of 1/6/04 (June 1st) in a TEXT function
is coming up January.

Formula is:
="TOTAL " & TEXT(MONTH(D1),"mmmm") & " " & YEAR(D1)
Where cell D1 = June 2004 (formatted) or 38139 (date
value). The result comes up TOTAL January 2004. Correct
format, incorrect date.

If I change the formual to:
="TOTAL " & MONTH(D1) & " " & YEAR(D1)
The result is TOTAL 6 2004. Correct month but incorrect
format.

Clearly something is going on with the TEXT function. Any
ideas? (I've confirmed that English (Australia) is the
option chosen for spell checking & format cells "special"
in excel).

Thanks
Sarah

 

[Norman Harker]

Hi Sarah!

The MONTH function returns a month number between 1 and 12. If you
format that number as mmmm, it will always return January as the date
serial numbers 1-12 are all in January 1900.

Try:

="TOTAL "&TEXT(D1,"mmmm yyyy")

 

[Peo Sjoblom]

Try

="TOTAL "&TEXT(D1,"mmmm yyyy")

the reason that you get January is that from

TEXT(MONTH(D1),"mmmm")

you get 6 and 6 formatted as mmmm is the same as January 6 1900 then when
you add the year it looks like January 2004

Try by putting 6 in a cell and format it as dd/mm/yyyy and you'll see
06/01/1900



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)