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.

--
Thanks!

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



"Saucer Man" <(E-Mail Removed)> wrote in message
news:4a15a477$0$2697$(E-Mail Removed)...
>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.
>
> --
> Thanks!
>

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)


"Saucer Man" wrote:

> 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.
>
> --
> Thanks!
>
>
>

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 !

Thanks for the tips everyone!


"Saucer Man" <(E-Mail Removed)> wrote in message
news:4a15a477$0$2697$(E-Mail Removed)...
>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.
>
> --
> Thanks!
>
>