Date format problem

  • Thread starter Thread starter Grace
  • Start date Start date
G

Grace

I have a cell C12 in an EXCEL worksheet of 12/31/2003. It is used,
mathematically, in many places and works fine - is formatted as 3/14/2001.

When I use the Month(c12), it shows up as a "J" for January. If I write c13
=text(month(C12),"mmmmmmmm"), C13 shows January, when I hit F2, F9, then
escape. If I hit F2 on cell C12, then F9 (then escape), it does correctly
show a 12, for the 12th month. How do I get it to show December, not
January?

Thx,
Grace
 
The formula =MONTH(C12) should return 12 not "J" or even"D"
The formula
=INDEX({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","D
ec"},1,MONTH(A1)) will return Dec
 
I'm sorry, it only returns J when I use the EXCEL date format shown as "M',
whihc I would think stands for the first letter of a month. Your idea is
good but isn't there some easier way, via formatting, than this index
function to make it show up as the name of the month?
 
Hi Grace,
I have a cell C12 in an EXCEL worksheet of 12/31/2003. It is used,
mathematically, in many places and works fine - is formatted as 3/14/2001.

When I use the Month(c12), it shows up as a "J" for January. If I write c13
=text(month(C12),"mmmmmmmm"), C13 shows January, when I hit F2, F9, then
escape. If I hit F2 on cell C12, then F9 (then escape), it does correctly
show a 12, for the 12th month. How do I get it to show December, not
January?
Click to expand...

write the Formula =C12 und use the NumberFormat MMMMM

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
I don't see this format as a number format, date format, or anything, for
that matter. Is it a custom format I have to create? Do you know why that
date format listed as "M" shows a "J" (for 12), if I use that?

If I want the full name of the month, do I need 8 M's?

Thx,
 
Hi Grace!

I fear that you are going round in circles.

=MONTH(C12)*29
Format mmm or mmmm will return the month name. Using
TEXT(MONTH(C12)*29,"mmm") avoids need for format and returns as text.

However, C12 is almost always a date. So:

=C12
Format mmm or mmmm

Or

=TEXT(C12,"mmm")

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Grace!

"mmm" for Jan, Feb etc

"mmmm" for January, February etc

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Grace,
I don't see this format as a number format, date format, or anything, for
that matter. Is it a custom format I have to create? Do you know why that
date format listed as "M" shows a "J" (for 12), if I use that?
Click to expand...

sorry it's a custom format.
If I want the full name of the month, do I need 8 M's?
Click to expand...

you can try it out yourself ;-)

M MM MMM MMMM MMMMM

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
Hi Norman,
What version of XL are you using? I am using XL2003
I put 1,2,3.....12 in A1:A12,
In B1 I entered = A1 and copied this down to B12
Formatted B1:B12 as MMMM and every cell displays January
 
<< sorry it's a custom format. >>

Thanks, Melanie. I guess I just figured there should be some canned
(nonm-custom) EXCEL formatting of the cell that would work, but I guess
there isn't.

I still don't understand why the 12 translates to a "J" under the canned "M"
date format. I thought M was for month, but it appears that it must mean
something else, like the 12th day of the year, which starts with a J.

Thx.
G
 
Hi Bernard!

A misunderstanding! I'm assuming A1 contains a date.

For month numbers 1-12, my easy method of securing names without using a
LOOKUP or INDEX is:

=A1*29
Format mmm or mmmm
or:
=TEXT(A1*29,"mmm")

The month numbers 1 to 12 multiplied by 29 all give dates in 1900 that
(fortunately) are in consecutive months.

But most often this isn't necessary because we have a date that we can use
and apply a simple mmm or mmmm format to.



--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I must have had a 'senior moment' and/or lack of coffee - I thought =C12 was
pointing to a formula with just the month number!!!
I realise my error just as I pressed Send. Thanks for being so understanding
in your reply

Bernard
 
Back
Top