Date Conversion Help
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! |
Date Conversion Help
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" wrote in message ... 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! |
Date Conversion Help
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! |
Date Conversion Help
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 ! |
Date Conversion Help
Thanks for the tips everyone!
"Saucer Man" wrote in message ... 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! |
All times are GMT +1. The time now is 10:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com