removing date data from a cell

H

hansjhamm

I use networkdays for my calculation...The 2 cells that the dates are
pulled from are set like such; From: Monday, May 8,2006 and Through:
Wednesday, May 10, 2006.

How would I go about removing the commas, colons, from, through and day
in VBA?


Thanks,

Hans
 
D

Dave O

Assuming the From and Through cells are *always* in the format
From colon space weekday comma space month space daynumber comma space
Click to expand...
year
and
Through colon space weekday comma space month space daynumber comma
space year

....then this code will convert the textual dates within those cells
into Excel-readable dates.

Sub test()
Dim From As Date, Thru As Date
From = DateValue(Mid(Range("a1").Value, InStr(1, Range("a1").Value, ",
Click to expand...
") + 1, Len(Range("a1").Value)))
Thru = DateValue(Mid(Range("b1").Value, InStr(1, Range("b1").Value, ",
") + 1, Len(Range("b1").Value)))


End Sub

This code works by ignoring the From: , Through: , and weekday name,
and converting the remainder of the string into a date using the
DateValue function in VBA.
 
H

hansjhamm

Dave,
When I ran this I got a Type mismatch error...what I put in was this;

Sub test()
Dim ClassBreakdownByStore As Workbook
Dim From As Date, Thru As Date
From = DateValue(Mid(Range("B1").Value, InStr(1, Range("B1").Value,
Click to expand...
",") + 1, Len(Range("B1").Value)))
Thru = DateValue(Mid(Range("b1").Value, InStr(1, Range("b1").Value,
",") + 1, Len(Range("b1").Value)))

End Sub

I am not understanding this somewhere that is why I am called a
"newbie" at this!


Thanks

Hans
 
Top