converting the date style

hi,

i`m having a bit of a problem converting the style of the date to another one.
What i`m doing is getting the contents from a cell then trimming it, then assigning it to a variable. I would like the end result to be diplayed as dd-mm-yyyy.
Initially the content of the cell is something like 12/06/06 11:15:03 AM.
I trim it down so only 12/06/06 is displayed, after this i am unable to change it to 12-06-06.
my code is as follows:
Sub ParseSaveAsName()

Dim sFileName As String
Dim datt As String
Dim directory As String
Dim strDate1 As Date
Dim strDate2 As Date

'find the date created cell
Cells.Find(What:="Date Created", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

'look at the next cell down which should contain the date
ActiveCell.Offset(1, 0).Activate

'assign the cell value to a variable.
Dim variable1 As String
variable1 = ActiveCell.Value
variable1 = Left(variable1, 10)

'find the end date cell
Dim endcell As Integer
endcell = Range("B1").End(xlDown).Row

'select the datecreated row again.
Dim datecreatedcolumn As Integer
Dim variable2 As String

Cells.Find(What:="Date Created", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
datecreatedcolumn = ActiveCell.Column

Cells(endcell, datecreatedcolumn).Select

'assign to variable
variable2 = ActiveCell.Value
variable2 = Left(variable2, 10)

strDate1 = variable1
strDate1 = Format(variable1, "dd-mm-yyyy")

strDate2 = variable2
strDate2 = Format(variable2, "dd-mm-yyyy")

MsgBox (strDate1)
MsgBox (strDate2)

'directory = ("\\thorntonap002\livedata\Epos_admin\Helpdesk\Polling reports\Chris' Polling Report\" + variable1 + " - " + variable2 + " generated polling report.xls")

'sFileName = Application.GetSaveAsFilename(directory)

'They have cancelled
'If sFileName = "False" Then Exit Sub
'ActiveWorkbook.SaveAs sFileName, FileFormat:=xlNormal


End Sub


any ideas???

thanks

Hi,

The potential problems that i could point out would be the lines.

'assign to variable
variable2 = ActiveCell.Value
variable2 = Left(variable2, 10)

strDate1 = variable1
strDate1 = Format(variable1, "dd-mm-yyyy")

strDate2 = variable2
strDate2 = Format(variable2, "dd-mm-yyyy")

The date is formatted and stored in the strdate variables, but variable1 & variable2 are used in framing the 'directory' path.

So try
variable1 = Format(variable1, "dd-mm-yyyy")
variable2 = Format(variable2, "dd-mm-yyyy")
and that should address your issue. i believe.

Also if the cell content is 12/06/06 11:15:03 AM. and the format is not going to changes you can use
variable2 = Left(variable2, 8)
variable1 = Left(variable1, 8)

Because only left 8 characters are data componet.

Try and let me know.

Best Wishes
MtpSuresh