Convert Number to look like Date format.

Q

Question_123

Hi group,

I have a cell on multiple sheets with the numberic value of 20040518.

Could someone point me in the right direction in converting this
number to look like 2004/05/18, I would like to use this in a macro to
convert them all.

Thanks.
 
G

Guest

I would the following formula to convert the data:
Say A1 is 20040518 then in Cell B1 use the following:

=date(left(A1,4),mid(A1,5,2),right(a1,2))
 
R

Ron de Bruin

If they all 6 digit then you can use this for column A
the date will be in Column B

Sub test()
Dim cell As Range
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
cell.Offset(0, 1).Value = _
Left(cell, 4) & "/" & Mid(cell, 5, 2) & "/" & Right(cell, 2)
Next
End Sub

And format the cells like this
jjjj/mm/dd
 
Top