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

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

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

 

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

@krishna345 

Here it is

image.png

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.

@krishna345 

 

1. Assuming your date is just stored as text.

=DATEVALUE(A1)

 

@SanthoshKunder 

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