DATE to MONTH problem (TEXT function)

[melwayconsultancy]

Hello everyone,

I want to convert dates to months in the MONTH column. I tried the TEXT function, it worked for some dates but the other dates were unable to be converted. Some dates, such as "." or "/" differ, but how can I resolve this issue? Thank you.

[#2]

@melwayconsultancy 

Apparently you use m.d.y date format. Try

=TEXT(SUBSTITUTE(B2, "/", "."), "mmmm")

[#3]

DATE: 4.1.2019 -> MONTH: =TEXT(B2;"mmmm") -> RESULT: November - This is ok
DATE: 6/25/19 -> MONTH: =TEXT(B3;"mmmm") -> RESULT: 6/25/19 - Not June.
Why is that?
The formula you sent did not work.

[#4]

@melwayconsultancy 

Apparently your system does not recognize 6/25/19 as a valid date. Instead, it treats it as text, so the date format is ignored.

What happens if you do the following:

• Select the column with the date/time values.
• Press Ctrl+H to activate the Replace dialog.
• Enter / in the 'Find what' box and . in the 'Replace with' box.
• Click 'Replace All'.

[#5]

@melwayconsultancy 

See if this formula works for you:

=TEXT(IF(ISTEXT(B2),DATE(2000,LEFT(B2,FIND("/",B2)-1),1),B2),"mmmm")

[#6]

@Hans Vogelaar 

Thanks it does work.