Can't get MONTH to format correctly 1

[Tonyjstone]

An unusual feature has started to appear on my Excel.
Whenever I use the =MONTH function in a cell formatted for 'mmm' or 'mmmm' or anything other than 'number', the only result I can get is Jan or January, regardless of the date entered.
The function does not produce the correct month reference unless I format the cell for number which does produce the correct month number.
I assume I have turned a switch somewhere, can anyone tell me where?

Sometimes I sits and thinks, sometimes I just sits.

 

[combo]

MONTH function returns month numbers: 1, 2 .. to 12, in date convention they are dates between Jan 1, 1900 and Jan. 12, 1900. So, formatted to display month, always display January.
To get month name, use TEXT function (=Text(A1,"mmmm")).

combo

 

[TonyJollans]

Hi Tony,

Interesting that you should say ..

... has started to appear on my Excel

It implies you used to get something else.


The reason is that formatting codes of "mmm", "mmmm", etc apply to Date values. The Month Function returns a value in the range 1 to 12 which, when interpreted as a date, is always in January. To see "May" or whatever, just put the date in the formatted cell - no need for the Month function at all.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[GlennUK]

I think you've got the wrong end of the stick.

=MONTH gives the month NUMBER of a date. So =MONTH(TODAY()) would give 3, which is the month number of March.

Therefore all dates will give a result between 1 and 12.

Now, this number should NOT be formatted as a date, as the result will be meaningless.

If, instead, you want a string of the month for a date use =TEXT(dateref,"MMM")

Glenn.

 

[Tonyjstone]

Thank you all for your help.

I seem to have been led astray by the very useful XLFunction guide by Peter Noneley which I use to expand my meagre knowledge of functions. That workbook demonstrates the =month function in the manner I expected, not, as it is and has been described by your good selves.
Once again, Thank you all.

 

[xlbo]

I think, in this instance (and I'm sure Ken will agree with me), we can let Peter off for this minor mishap - given that the rest of his xl dictionary is one of the best pieces of work out there for helping with Excel.

You are correct - Peter's explanation is incorrect - it just happens to work as the date being used is in January and that's what the MONTH function will return (as ably explained by Combo, Tony & Glenn)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question