Parse a date

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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 /.