You will have to use a formula to convert them - they won't be recognised as dates by Excel.
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change the reference to A1 as required and choose your date format.
This is a discussion on Convert number to date within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are ...
I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are infact dates - I am struggling to format them as such. HELP!
Many thanks in advance
You will have to use a formula to convert them - they won't be recognised as dates by Excel.
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change the reference to A1 as required and choose your date format.
=TEXT(A1,"0000-00-00")+0
Format the cell containing this formula with an appropriate date format.
[ This Message was edited by: Mark W. on 2003-01-31 13:30 ]
Cool!On 2003-01-31 13:29, Mark W. wrote:
=TEXT(A1,"0000-00-00")+0
Format the cell containing this formula with an appropriate date format.
[ This Message was edited by: Mark W. on 2003-01-31 13:30 ]
I am trying to convert 38370 to date 19-Jan-2009.
Using formula =DATE(RIGHT(A1,2),MID(A1,5,2),LEFT(A1,4)).
But it gives starnge date.
Number 38370 is generated by converting date "19-Jan-2009" to general format in excel sheet.
Just Format the cell as
dd-mmm-yyyy
.
Biff
Microsoft MVP - Excel
Using Excel 2002, 2007:2013
KISS - Keep It Simple Stupid
Like this thread? Share it with others