Month function

  • Thread starter Thread starter CindyH
  • Start date Start date
C

CindyH

Hello,

I have Excel 97 with the first service pack at home. I don't have an
internet connection at home, so can't install the second service pack.

Now that said...
In A1 I have:
10/8/04

in A2 I have this:
=MONTH(A1)

A2 actually says "January" without the quotes.

No matter what date I put in A1, A2 always says January. Is there any way at
all to fix this?

TIA!!!
Cindy
 
Yes it is. I've tried it all kinds of ways, including order and formatting.
I just also tried it on this computer with SP 2 and it's still doing the
same thing.
 
I have 10/08/2004 in A1 and it is formatted as a date. I put in what you
said and A2 now says True. Is that good or bad?
 
A2 actually says "January" without the quotes.

No matter what date I put in A1, A2 always says January. Is there any way at
all to fix this?
Click to expand...

You have A2 formatted as "mmmm" so this is the expected result.

If you want the result of the MONTH function, which is a number 1-12, then you
need to format A2 as General or Number.

If you want the spelled out month of the date you have in A1, then either:

1. Change the formula in A2 to =A1 and leave the format alone.

or

2. Use the formula =TEXT(A1,"mmmm")


--ron
 
Aha! Thank you, thank you, thank you! I just couldn't seem to get that. If
it had been a number, rather than the head-scratching January, I may have
figured that one out. Ah, well. Such is that.

Thanks again so very much,

Cindy

 
Hi
now i see your problem. MONTH always returns a number (1-12). You
probably need
=TEXT(A1,"MMMM")
 
=month(A1) will return 1 through 12.

In excel, dates are just numbers (format one as General and you'll see it) from
a base date (usually 12/31/1899 for Windows).

And the numbers 1 through 12 would represent the first 12 days of 1900.

And the first twelve days of 1900 occur in January.

You could have used,
=a1
and formatted it as mmmm, too.

Or just show the month and date in A1 by using a custom format of:

mmmm mm/dd/yyyy
 
Cindy

MONTH(A1) returns the number corresponding to the month, which in this case is
8 or 10 depending upon your short date settings in Windows. I return 8 which
is August.

Since 8 or 10 is a number and no longer associated with a date, formatting
"mmmm" returns January because Excel doesn't know it is a month.

Ron's method of entering =A1 and formatting to "mmmm" returns the correct
month because it is still treated as a valid date.

Gord Dibben Excel MVP
 
Hi Cindy,
Perhaps you have Transition Formulas evaluation turned on
Tools, Options, Transition -- turn off all transition options.

which would change your content to the equivalent of
=10/8/04 would be .3125 which sould be about 7:30 AM
Jan 1, 1900 you could use Excel to get a more accurate figure
by formatting as a date followed by a time
i.e. mmm dd, yyyy hh:mm:ss

To check what you actually have see
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
 
Aha! Thank you, thank you, thank you! I just couldn't seem to get that. If
it had been a number, rather than the head-scratching January, I may have
figured that one out. Ah, well. Such is that.

Thanks again so very much,
Click to expand...

You're welcome.


--ron
 
Back
Top