Return correct month in text

[Dryver14]

Hi all,

I'm in the UK so 1st march is 01/03/18 ...everyone else is wrong!

Including excel it seems. I am using =text(month(A5),"mmmm") which is returning January.

Strangely =month(A5) returns 3, its the text that's converting it to 1

I've tried =text(month((A5)+3,"mmmm") still produces January

Any Ideas?

 

[steve the fish]

You dont use the month bit. You are saying =text(3,"mmmm"). 3 is a valid date. 3rd January 1900 hence January being returned

 

[Rick Rothstein]

Hi all,

I'm in the UK so 1st march is 01/03/18 ...everyone else is wrong!

Including excel it seems. I am using =text(month(A5),"mmmm") which is returning January.

Strangely =month(A5) returns 3, its the text that's converting it to 1

I've tried =text(month((A5)+3,"mmmm") still produces January

If your cells have a number followed by an ordinal designation followed by a space followed by the month name, all as text, and you want the month name from the cell, this formula should work...

=TRIM(MID(A1,5,10))

 

[jtakw]

Hi,

I think you don't need month in =text(month(A5),"mmmm") , see sample below:

Book1
	A	B	C	D	E
1	01/03/2018		3	March	January
Sheet34

Cell Formulas
Range	Formula
C1	=MONTH(A1)
D1	=TEXT(A1,"mmmm")
E1	=TEXT(MONTH(A1),"mmmm")

 

[dave2018]

U need to add an "artificial" date... like the following formula

=TEXT(A5&"/1/1901","mmmm")

it says 3/1/1901, with the 3 being the value in A5, and adds the rest, so that the text function will read it correctly.

 

[Dryver14]

I don't have that situation Rick,

People are inputting a date in format 01/01/18 and from that I have a cell below to get the month name for an indirect formula where the sheet tabs are month names

 

[Dryver14]

Steve and Jtakw.... you get the lollipops.

Thanks all

 

[dave2018]

Right.
i see that misunderstood your question

 

[jtakw]

You're welcome, give me the Strawberry one.

 

[Dryver14]

Dave,

I did try your formula as it seemed like it may come in useful at some time.

it generated the following 43160/1/1901