Trying to parse a date to save appended to filename with spaces, don't want /.

So I use instr to locate the position of the first / so I can parse the day
and month.
How do I use instr to locate the second / in the date?
TIA

Hi Headley,

Will this example help? Reset the format in the double quotes to your date
format.
Look up 'Replace function' in help for more info.

Sub Test_Instr()

Dim testDate As Date
Dim testString

testDate = #2/4/2007# 'Actual date

'Replaces all occurrences of /
testString = Replace(Format(testDate, "dd/mm/yyyy"), "/", "")

End Sub


"headly" wrote:

Trying to parse a date to save appended to filename with spaces, don't want /.

So I use instr to locate the position of the first / so I can parse the day
and month.
How do I use instr to locate the second / in the date?
TIA

Should have said removes all occurrences of /
If you want to relace all occurrences of / with another character
then put that character within the last double quotes. Space is valid.

Also you don't need to use replace function. Simply use the following:-

testString = Format(testDate, "dd mm yyyy")

You can choose any format you like. "yyyy mm dd" is valid and will
make your files sort in proper date order.

Regards,

OssieMac

"headly" wrote:

Trying to parse a date to save appended to filename with spaces, don't want /.

So I use instr to locate the position of the first / so I can parse the day
and month.
How do I use instr to locate the second / in the date?
TIA

shi
are you doing this with formula or macro? I have never heard of instr. but
since you used the word parce....
I took today's date and did a text to column using delimited parse, other =
/ and it parsed the date numbers into 3 cells omitting the /. I then used
this formula...
=C6&D6&E6 to achieve the results 7222007.
i used this formula =C6&D6&RIGHT(E6,2) to achieve the results 72207.
macro...
Sub rename()
Dim nam1 As String
Dim nam2 As String
Dim nam3 As String
Dim sd As Date

sd = InputBox("Enter the date")
nam1 = Day(sd)
nam2 = Month(sd)
nam3 = Year(sd)

ActiveWorkbook.SaveAs Filename:= _
"C:\yourpath\filename" & nam2 & " " & nam1 & " " & nam3 & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
end sub

regards
FSt1

trying to parse a date to save appended to filename with spaces, don't want /.

So I use instr to locate the position of the first / so I can parse the day
and month.
How do I use instr to locate the second / in the date?
TIA