Displaying month or year as text

[Monovian]

Using Excel 2007. I have a date of 15-Aug-09 in a cell but if I use the
function Text(Month(cell),"mmm") it shows "Jan". If I try with the Year
function I get "1905". Entering just Month(cell) gives me the correct answer
of "8". What is wrong please?

 

[Mike H]

Hi,

Try

=TEXT(A1,"mmm")

Mike

 

[Monovian]

Thanks - why didn't I think of that?

Hi,

Try

=TEXT(A1,"mmm")

Mike

 

[Mike H]

Hi,

Maybe brain blockage. You could have done it your way like this

=TEXT(MONTH($A$1)*29,"mmmm")

Mike

 

[Ron Rosenfeld]

Using Excel 2007. I have a date of 15-Aug-09 in a cell but if I use the
function Text(Month(cell),"mmm") it shows "Jan". If I try with the Year
function I get "1905". Entering just Month(cell) gives me the correct answer
of "8". What is wrong please?

To explain what is wrong, you must understand the following:

Excel stores dates as serial numbers with 1 = Jan 1 1900

Month(cell) returns a number from 1-12 (the months of the year as numbered).

Text looks at its "value" argument and formats it according to the
"format_text" argument. In your case, value = 8. As a date, 8 = 8 Jan 1900;
formatted as "mmm" --> Jan.

If you want "mmm" to apply to 15-Aug-09, then you must use that as the value
argument.

i.e. =TEXT(cell, "mmm")
--ron

 

[alanglloyd]

Using Excel 2007. �I have a date of 15-Aug-09 in a cell but if I use the
function Text(Month(cell),"mmm") it shows "Jan". �If I try with the Year
function I get "1905". �Entering just Month(cell) gives me the correct answer
of "8". �What is wrong please?

Why not use the cell format to get the display you want from a date
value. Right click on the cell and select Format Cells > Number. Then
enter mmm or mmmm in the Type entry box.

Alan Lloyd