Converting Dates

[brettvba]

I have a few hundred dates
reading 1020312 etc
which I want to convert to a date either
120302 or tuesday 12 march 2002
can this be done?

[Jay Petrulis]

One way:

With the data in A1,

=DATE(LEFT(A1,3)+1900,MID(A1,4,2),RIGHT(A1,2))

and format the cell as the date format you want.

Jay

EDIT: If you are working with dates prior to 2000 and the first 2 digits (rather than 3) are 99 for example, use the more robust, but longer formula:

=DATE(LEFT(A1,LEN(A1)-4)+1900,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))



[ This Message was edited by: Jay Petrulis on 2002-04-15 14:30 ]

[jamienwood]

Try this it'll change those numbers to dates wether it relevent dates i dunno gut you could merge the last formulea with this

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/15/2002 by Jamie Wood
'

'

Range("B18:B22").Select
Selection.NumberFormat = "yy/dd/mm"
End Sub

[brettvba]

Jay nothing seems to show up in the cell when you put the formula in there but when you hover over the top of the cell it tells you the correct date??

[brettvba]

sorry no it does work for some reason It brought up the answer in white text Duh

[brettvba]

Thanks Alot Jay thats great.