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?

Hi,

Try

=TEXT(A1,"mmm")

Mike

"Monovian" wrote:

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?

Thanks - why didn't I think of that?

"Mike H" wrote:

Hi,

Try

=TEXT(A1,"mmm")

Mike

"Monovian" wrote:

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?

Hi,

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

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

Mike

"Monovian" wrote:

Thanks - why didn't I think of that?

"Mike H" wrote:

Hi,

Try

=TEXT(A1,"mmm")

Mike

"Monovian" wrote:

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?

On Sun, 13 Sep 2009 02:43:01 -0700, Monovian
wrote:

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

On Sep 13, 10:43�am, Monovian
wrote:
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