Convert date to different format

[sharky12345]

I have a date in a cell which is formatted as "dd/mm/yyyy" but I need to use that date value as part of a filename later, so want to convert it in VBA to "dd_mm_yyyy".

Can someone show me how please?

 

[Vbagreenhorn1]

try this:

[A1] = Left([A1], 2) & "_" & Mid([A1], 4, 2) & "_" & Right([A1], 4)

 

[sharky12345]

Thanks,

can you help me though - this is what I have currently in the routine which points to the cell holding the value;

Sheet2.Range("C10").Value

So can you show me how your suggestion would look in that line?

 

[Vbagreenhorn1]

like this?

X = Sheet2.Range("C10").Value
[A1] = Left(X, 2) & "_" & Mid(X, 4, 2) & "_" & Right(X, 4)

 

[sharky12345]

Sorry, I should have explained better - how would it look in the line I have with represents a workbook path, like this;

& Sheet2.Range("B9").Value & " - " & [A1] & ".xlsx"

 

[Vbagreenhorn1]

i'm confused so the date you want to use in the file name is located in cell C10 of sheet 2?

X = Sheet2.Range("C10").Value

............ & Sheet2.Range("B9").Value & " - " & Left(X, 2) & "_" & Mid(X, 4, 2) & "_" & Right(X, 4) & ".xlsx"

 

[Andrew Fergus]

like this?

X = Sheet2.Range("C10").Value
[A1] = Left(X, 2) & "_" & Mid(X, 4, 2) & "_" & Right(X, 4)

Would it be safer to use this method?

Format(.Range("A1").Value, "dd_mm_yyyy")

Andrew

 

[sharky12345]

Guys - thanks, both suggestions work, and sorry for the confusion, I am both confused and confuse easily!