Forum Discussion

David Wess's avatar
David Wess
Copper Contributor
Sep 06, 2017

Excel date formats

I have several hundred dates stored as follows. These are all dates, but some are missing the leading '0' (for example 3302016 should be 3/30/2016). How can I convert all these fields to properly formatted dates?

 

 

 3302016
 3302016
73020152082016
73020152082016
73020152082016
excel
  • Tanya Denton's avatar
    Tanya Denton
    Steel Contributor
    Sep 06, 2017
    Go to the Store Add-ins and search for 'Transform Data by Example. and try this (there are instructions)
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Sep 06, 2017

      Add-in has pluses and minuses, but in any case it transforms source column into another one and works column by column.

       

      Since source cells are numbers, not text with leading zeros, i don't see the way to convert them in place, if only macros. Otherwise Text to Columns works.

       

      For the very limited task only show that numbers as dates we may apply to them custom format

      00"/"00"/"0000

      but that won't be real dates - we can't make calculations with them, compare, etc.

       

      To convert these numbers into the dates in the separate column(s) few formulas could be used. If local default date format is dd/mm/yyyy perhaps the easiest is (for number in A1)

      =DATEVALUE(TEXT(A1,"00/00/0000"))

      Otherwise parse the number on year, month and date and use DATE() to convert, like

      =DATE(RIGHT(A1,4),(TRUNC(A1/1000000,2)-TRUNC(A1/1000000))*100,TRUNC(A1/1000000))

       

Resources