Date from string

[Robert_L_Ross]

I have a unique problem. When I export a report from a program, the dates
are formatted as mm/dd/yyyy:
09/01/2008

When other users export the same report from the same program, the dates are
formatted as m/d/yyyy:
9/1/2008

I don't know if it's an Excel issue, Windows or even the source program, but
that's not important.

The export has cell B3 with this value (on my machine):
09/01/2008 to 09/30/2008

When I go into a macro with the following line:
Message = MsgBox(InStr(1, "B3", " to "), vbOKOnly, "test")
I get a dialog box with "0"

This makes no sense...when I use the Find function in the spreadsheet it
works:
=FIND(" to ",B3,1) Returns 11

I'm trying to find a way in the VB code to account for the different date
formats...if I can find where " to " starts, I can trim the value. I need
the value to determine a name for the file based on it's contents.

Thanks!!

 

[Dave Peterson]

"B3" is the text B3--not what is in some worksheet in cell B3.

Message = MsgBox(InStr(1, worksheets("sheet999").range("B3").value, " to "), _
vbOKOnly, "test")

You may have to include the workbook that that worksheet belongs to, too.

 

[Robert_L_Ross]

Dave,

Thanks...after I posted that and just before I left work I remembered I
needed to specify the cell value...in this case I didn't need to specify the
sheet name since I'm looking at the active sheet when the macro runs.

Thanks!

 

[Dave Peterson]

If you have a column of cells that contain this kind of information:
09/01/2008 to 09/30/2008

You could insert a column (or two) and use data|text to columns to parse the
string. You'll be able to specify the format for each date field (mdy), too.

And if you need a macro, you could record one when you do it manually to get the
syntax.