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

excel
Formulas and Functions

5 Replies

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

      SanthoshKunder 

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

  • 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