Extracting date from cell

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

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
 
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
 
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
Click to expand...

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.
Click to expand...

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
 
Back
Top