Forum Discussion

dblunden's avatar
dblunden
Copper Contributor
Jan 29, 2020

Change date text formatted as dd/mm/yyyy hh:mm:ss AM/PM into date serial no.

I have raw cvs file dump with text strings I need to return in a new column with the date/excel "serial number":

the cell contains for example: "23/07/2019 12:12:11 PM"

I need to return a serial date so that I can overlay data with discreet times into a pivot table.

 

excel

6 Replies

Sort By
  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 30, 2020

    dblunden 

     

    OK, once the bug bites it's hard to let it go.

     

    Here's a parsing formula that returns the datevalue, assuming your string is in cell A1

    =DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4))

    will return the date value.

     

    Enjoy!

    • dblunden's avatar
      dblunden
      Copper Contributor
      Jan 30, 2020
      Wa…..Fantastic. You have no idea how helpful that was.
      • pedroreis's avatar
        pedroreis
        Copper Contributor
        Apr 22, 2021

        Hi,

         

        Having real issues in converting a date in excel. It was so easy with google sheets.

         

        Can anyone lend me a hand? (check attachment)

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 30, 2020

    dblunden 

     

    That's a tricky one, because the dates are displayed in a European manner.

    If it were "7/23/2019 12:12:11 PM"

    then this formula works easily

    =DATEVALUE(LEFT(A1,FIND(" ",A1)))

     

    But with "23/07/2019 12:12:11 PM" it returns an error message.

     

    It certainly is possible to parse the date and convert it to separate numbers for month, day and year....but I'm hoping there's somebody else here who can do something more directly.

Resources