Forum Discussion

Tryggvi Bjorn Stefansson's avatar
Tryggvi Bjorn Stefansson
Copper Contributor
Sep 30, 2018

Translating serial numbers into date codes

I have an excel file with a column with dates in the format dd.mm.yyyy. In some of the cells, there are more than one date or commas and semi commas. I want only to have ten digits counted from the left in the cell ( I used the left function (LEFT(A2;10)). The 10 digits dates appear in the next column and most of the dates appear in the format dd.mm.yyyy as planned but some of them appear as a serial number (For example: instead of 13.08.2007 I get 39307).

 

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 30, 2018

    Better to use =VALUE(LEFT(A2;10)) and apply date format to your target column (I assume dd.mm.yyyy is your default date format)

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Sep 30, 2018

    Hi,

     

    I suggest to use the below formula to extract each part of the date and format it in the desired format:

    =TEXT(DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)),"dd.mm.yyyy")

     

    Hope that helps

Resources