Date Conversion Help

[Saucer Man]

I have a macro which imports a date into one of my excel cells from a .csv
file in a strange format. For example, if the date is 5-20-09, it is
imported in this format...

##20090520123345

I want the macro to change it to...

05/20/09

Currently I am searching the cell and if I find the XX in the first two
positions, I am just replacing the whole string with DATE. The problem is
that the date might be a date other than today.

How can I manipulate and truncate the string once I find the entries that
start with XX? The numbers 12345 in the above example are not constant.

 

[Bernie Deitrick]

Saucer man,

Perhaps:

Dim strDate As String
Dim myDate As Date

strDate = "##20090520123345"
myDate = DateSerial(Mid(strDate, 3, 4), Mid(strDate, 7, 2), Mid(strDate,
9, 2))
MsgBox myDate

HTH,
Bernie
MS Excel MVP

 

[Joel]

MyDate = "##20090520123345"
MyYear = Mid(MyDate, 3, 4)
MyMonth = Mid(MyDate, 7, 2)
MyDay = Mid(MyDate, 9, 2)
MyHour = Mid(MyDate, 11, 2)
MyMinute = Mid(MyDate, 13, 2)
MySecond = Mid(MyDate, 15, 2)
Mytime = DateSerial(MyYear, MyMonth, MyDay)
Mytime = Mytime + TimeSerial(MyHour, MyMinute, MySecond)

 

[Modeste]

Bonsour® Saucer Man avec ferveur ;o))) vous nous disiez :

I have a macro which imports a date into one of my excel cells from a
.csv file in a strange format. For example, if the date is 5-20-09,
it is imported in this format...

##20090520123345

I want the macro to change it to...

05/20/09

suppose your date datas are in column C :
only for the dates :

Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
OtherChar:="#", FieldInfo:=Array(Array(0, 9), Array(2, 5), Array(10, 9)), _
TrailingMinusNumbers:=True

for dates and times détails :
Warning Insert 3 columns before column "D"
(to prevent overwritting existing other datas)

Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
OtherChar:="#", FieldInfo:=Array(Array(0, 9), Array(2, 5), Array(10, 1), Array(12 _
, 1), Array(14, 1), Array(16, 1)), TrailingMinusNumbers:=True

;o)) Assume your know how to correct time reconstruction...

cheers !

 

[Saucer Man]

Thanks for the tips everyone!