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.

7 Replies

@melwayconsultancy 

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.

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

@melwayconsultancy 

See if this formula works for you:

 

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

@HansVogelaar 

Thanks it does work.

great help!!!!! thanks a lot!
Worked for me! Thank you!