Formula =TEXT(2017-11-03, "mmm") returns "00", I was expecting "November"...

[EmmaFia]

Hi all,

This feels like a silly problem but I just cannot work it out...

I want to display the month name in one cell by using the date in another cell. By googling I found out that the TEXT function should be able to this according to =TEXT(A1, "mmm"). However, when I apply the function I receive a double zero instead of the months name. It gives me the same result if I put the date in another cell or if I write it directly in the formula. What am I missing??

This is what I want:
A1: 2017-11-03 B1: November

This is what I get:
A1: 2017-11-03 B1: 00

Please help... =)
/Emma
[I am on a Windows 10 laptop with an Excel 365 subscription, language setting English.]

 

[etaf]

change the format of the cell B1 to be MMMM
then just use
=A1
the Date will now also be in B1 , but the cell format will provide how it is displayed

However , it works for me using your method
=TEXT(A1,"MMMM")

are you sure A1 isd actually a date
change the A1 cell format to Number
and see of the date changes to a number

the date 3rd Nov 2017 should change to 43042

 

[Special-K99]

For TEXT(A1,"mmmm") to work A1 has to be an internal Excel date.
2017-11-03 as an internal Excel date is 43042

TEXT(43042,"mmmm") would produce November

Use

TEXT(DATEVALUE("2017-11-03"),"mmmm")

 

[steve the fish]

What bothered me is how you got 00?? I cant replicate that no matter what i try.

 

[Special-K99]

What bothered me is how you got 00?? I cant replicate that no matter what i try.

Nor me. Cell format perhaps?

 

[EmmaFia]

Thanks to everyone who responded. I just worked it out!!

Everything was in order with formats and so on, no issues there. The solution appeared when I switched to capital letters... It turns out that when I write "mmmm" I get "00", but when I write "MMMM" I get "November". Sometimes it is the small things...

Anyway, thanks a lot for your support!!