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.?

Hi

Select the range with dates. Format as General. From Data menu, select (with
dates range remaining selected) TextToColumns. Finish.


Arvi Laanemets



"EAB1977" <[email protected]> wrote in message
news:[email protected]...
> 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.?
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.?
> >
>
>
>