DATE to MONTH problem (TEXT function)

Copper Contributor

Screenshot 2023-04-14 at 11.45.59.png


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.

5 Replies


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

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

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.


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'.


See if this formula works for you:



@Hans Vogelaar 

Thanks it does work.