Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date from string
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date from string
"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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date from string
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" wrote: "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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date from string
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. Robert_L_Ross wrote: 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" wrote: "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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
VBA convert day and date from text string to Excel date | Excel Programming | |||
Is the string a date? | Excel Programming | |||
changing a string date into a 'date' | Excel Programming | |||
Converting a string date into a Excel Date | Excel Programming |