I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

give an example of the format of the date

mmddyyyy
yyyymmdd

--


Gary Keramidas
Excel 2003


"Fred Smith" wrote in message
...
I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

maybe you meant sth. like

ReportDate = Right(Range("L3"),8) - 1

?

On 9 Kwi, 06:22, "Fred Smith" wrote:
I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

Sub Dte()
Dim ReportDate As Date, MyStr As String
MyStr = Range("L3")
ReportDate = Right(MyStr, 8)
Range("M3") = ReportDate - 1 'subtract 1 day
End Sub

This will put the date minus 1 day, into the cell to the right (M3).

Mike F
"Fred Smith" wrote in message
...
I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

On Thu, 8 Apr 2010 21:22:58 -0700, "Fred Smith" wrote:

I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

CDate(Right(Range("L3").Value, 8)) - 1

This assumes that the date format in the string is mm/dd/yy

VBA can get funny with non-US date formats.
--ron

Thanks, Ron. That's the one that worked for me.

Fred.

"Ron Rosenfeld" wrote in message
...
On Thu, 8 Apr 2010 21:22:58 -0700, "Fred Smith"
wrote:

I have a text in a cell which ends with a date. I would like to extract
the
date. I tried the following:

Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1

but got a type mismatch error.

How do I extract the last 8 characters of a cell into a Date variable in
VBA?

Thanks,
Fred

CDate(Right(Range("L3").Value, 8)) - 1

This assumes that the date format in the string is mm/dd/yy

VBA can get funny with non-US date formats.
--ron

On Fri, 9 Apr 2010 17:06:38 -0700, "Fred Smith" wrote:

Thanks, Ron. That's the one that worked for me.

Fred.

Glad to help.
Thanks for the feedback.
--ron

On Fri, 9 Apr 2010 17:06:38 -0700, "Fred Smith" wrote:

Thanks, Ron. That's the one that worked for me.

Fred.

By the way, there are other methods that can work also, but the one I posted
seemed the best "self-documenting" one for me.

You could, of course, dim a variable of type Date, and then set the cell equal
to that variable. That would also force the conversion if the cell contents
were text. But, it's not as obvious that is what is going on.
--ron