Forum Discussion
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 for...
Go to the Store Add-ins and search for 'Transform Data by Example. and try this (there are instructions)
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))
