Convert extracted text into a date format for validation

Copper Contributor

I have extracted a value into a cell from a text field in an external system and the value extracted is "20-Sep-2023"
If I write a formula in excel, to compare this date against today(), it does not work because it treats this value("20-Sep-2023") as a text field

Can you please suggest, how should I convert the extracted text("20-Sep-2023") into a date so that, it can work in formulas.

5 Replies


That could be

=DATE(RIGHT(A1,4), MONTH(1&MID(A1, 4,3)),LEFT(A1,2) )

If you have column with such date it's easier to select it, Data->Text to Columns, no delimiters and on third step select Date and DMY. 

@Sergei Baklan , Thank you. I got #VALUE! error with this formula


So, I tried power query and it resolved the issue. 


Here it is


Perhaps in your case text has some other non-printable characters.

Power Query is good enough, the only it puts result into another place. Again, Text to Columns could work if to transform in place.



1. Assuming your date is just stored as text.




IMHO, it depends on regional settings. If DATEVALUE() works, with copy/pasting such text into Excel it shall recognize it as date.