Generate file name with date from cell (not current date)

[RyanT]

My scenario: A time log for the week that is updated multiple times per day. Right now I have a command button that I click the last day of the week which advances the dates in my columns to the next week and clears the old time data, it then prompts for the a new file name. Would like my file name to be generated as “TimeSheet MyName_WeekEndingDate” (in MM-DD-YY format)

I can manage to format the CURRENT date in a M-D-YY format, but it always uses a MM/DD/YY format (which is invalid because of the “ / ” ) when I use the date stored in a cell even if the cell format is something other than m/d/y... Does anyone know of a workaround?

This is what I’m working with:


'Advance dates and Save.
Range("A1") = Range("A1") + 1
Range("F5") = Range("F5") + 7

Dim FileName As String, WeekNumber As String, MyName As String

WeekNumber = Range("A1").Value
MyName = Range("A29").Value
FileName = "Time Sheet " & MyName & " Week #" & WeekNumber & " Ending " & Format$(Date, "mm-dd-yyyy")
ChDir _
"E:\My Documents\time sheets\Test"
ActiveWorkbook.SaveAs FileName:=FileName

End Sub

Thanks Much

[Bob Phillips]

Is it not just



FileName = "Time Sheet " & MyName & " Week #" & WeekNumber & " Ending " & Format$(Range("A1").Value, "mm-dd-yyyy")

[mdmackillop]

If you need to sort these files by date order, consider using "yyyy-mm-dd" format.

[RyanT]

Yeah that's all i needed to do, i swear i tried that too..
Thank you very much