Convert extracted text into a date format for validation

[krishna345]

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.

[#2]

@krishna345 

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. 

[#3]

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

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

[#4]

@krishna345 

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.

[#5]

@krishna345 

1. Assuming your date is just stored as text.

=DATEVALUE(A1)

[#6]

@SanthoshKunder 

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