Forum Discussion

krishna345's avatar
krishna345
Copper Contributor
Sep 26, 2023

Convert extracted text into a date format for validation

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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 26, 2023

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. 

  • krishna345's avatar
    krishna345
    Copper Contributor
    Sep 26, 2023

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

     

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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 26, 2023

      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.

Resources