Extracting date from cell

[Fred Smith]

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

 

[Gary Keramidas]

give an example of the format of the date

mmddyyyy
yyyymmdd

 

[Jarek Kujawa]

maybe you meant sth. like

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

?

 

[Mike Fogleman]

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

 

[Ron Rosenfeld]

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

 

[Fred Smith]

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

Fred.

 

[Ron Rosenfeld]

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

Fred.

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

 

[Ron Rosenfeld]

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