Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Text dates into dates
Hello everyone,
I am having a issue that I hope someone can help me with I have a Excel sheet that, when completed, uploads to a Lotus Notes database and the proper recipients are notified. I am having some trobule converting the text dates into real dates. The code works about 90% of the time, but I would like it to work ALL the time. The text dates can have 3 dates. 1/1/2006 = 8 chrs 1/11/2006 = 9 chrs 11/11/2006 = 10 chrs To convert this to useable dates, I do the following: datFirst = Trim(Left(Range("C6").Value, 10)) datLast = Trim(Right(Range("C6").Value, 10)) ....because the dates can look like this: 1/1/2006 to 11/11/2006 When I use the code as typed, I get a data error that there is a Type Mismatch, and I know why that is since if you take the first part of the code (datFirst), it would come out as 1/1/2006 t. Is there anyway I can eliminate this? Or am I S.O.L.? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Text dates into dates
Hi
Select the range with dates. Format as General. From Data menu, select (with dates range remaining selected) TextToColumns. Finish. Arvi Laanemets "EAB1977" wrote in message oups.com... Hello everyone, I am having a issue that I hope someone can help me with I have a Excel sheet that, when completed, uploads to a Lotus Notes database and the proper recipients are notified. I am having some trobule converting the text dates into real dates. The code works about 90% of the time, but I would like it to work ALL the time. The text dates can have 3 dates. 1/1/2006 = 8 chrs 1/11/2006 = 9 chrs 11/11/2006 = 10 chrs To convert this to useable dates, I do the following: datFirst = Trim(Left(Range("C6").Value, 10)) datLast = Trim(Right(Range("C6").Value, 10)) ...because the dates can look like this: 1/1/2006 to 11/11/2006 When I use the code as typed, I get a data error that there is a Type Mismatch, and I know why that is since if you take the first part of the code (datFirst), it would come out as 1/1/2006 t. Is there anyway I can eliminate this? Or am I S.O.L.? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Text dates into dates
Another solution is to use =DATEVALUE() function in the worksheet. It does a
very good job of converting text strings into dates. Just be sure to format the cell containing the function as date. -- Gary''s Student "Arvi Laanemets" wrote: Hi Select the range with dates. Format as General. From Data menu, select (with dates range remaining selected) TextToColumns. Finish. Arvi Laanemets "EAB1977" wrote in message oups.com... Hello everyone, I am having a issue that I hope someone can help me with I have a Excel sheet that, when completed, uploads to a Lotus Notes database and the proper recipients are notified. I am having some trobule converting the text dates into real dates. The code works about 90% of the time, but I would like it to work ALL the time. The text dates can have 3 dates. 1/1/2006 = 8 chrs 1/11/2006 = 9 chrs 11/11/2006 = 10 chrs To convert this to useable dates, I do the following: datFirst = Trim(Left(Range("C6").Value, 10)) datLast = Trim(Right(Range("C6").Value, 10)) ...because the dates can look like this: 1/1/2006 to 11/11/2006 When I use the code as typed, I get a data error that there is a Type Mismatch, and I know why that is since if you take the first part of the code (datFirst), it would come out as 1/1/2006 t. Is there anyway I can eliminate this? Or am I S.O.L.? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Text to Dates | Excel Worksheet Functions | |||
converting text strings into dates | Excel Worksheet Functions | |||
Converting Text Values to Dates | Excel Discussion (Misc queries) | |||
imported text data converting to dates | Excel Discussion (Misc queries) | |||
Converting text to dates | Excel Discussion (Misc queries) |