How to convert dates in my Sub

[thomas.szwed]

Hi there,

I have the following sub that basically;-

- looks to two paticular cells on my spreadsheet
- uses these and a set piece of text to autofill the filename box of the 'Save As' screen

What i am looking to do is convert the format of the data from a cell the code looks to, into a different type.

Here is the code;

Sub SaveToFile()

Dim filename As Variant

With ActiveSheet

If .Range("D3").Value = "" Or .Range("G9").Value = "" Then

MsgBox "Both Name and Effective Date must be entered in order to Save", vbOKOnly + vbExclamation, "Save File"
Else

filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
End If
End With
End Sub

The cell is G9 and always contains a date in the format 28 July 2003. I would like the code to convert it into 28/07/03 and use that in the rest of the code?

Could someone tell me how i would build this in?

Thanks in advance!

[Bob Phillips]

You cannot use a slash in a filename, so I have used a hyphen in its place



Sub SaveToFile()

Dim filename As Variant

With ActiveSheet

If .Range("D3").Value = "" Or .Range("G9").Value = "" Then

MsgBox "Both Name and Effective Date must be entered in order to Save", vbOKOnly + vbExclamation, "Save File"
Else

filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Format(Range("G9").Value, "dd-mm-yyyy")
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then

ActiveWorkbook.SaveAs filename
End If
End If
End With
End Sub

[thomas.szwed]

I'm not sure this does the trick XLD? does this actually format the cell and so when i return out of the sub i find the date in that cell has changed?

Or is it just formatting it within the code?

Also if i wanted it in the format dd.mm.yy - what would i use?

Thanks in advance!

[Bob Phillips]

It doesn't touch the cell, just the output format.

Format(Range("G9").Value, "dd.mm.yyyy")

[thomas.szwed]

arrr right i see.

but i only want two digits for the year. would it just be dd.mm.yy??

Ta

[Bob Phillips]

Yep.