Having problem getting date into format: yyyy/mm/dd

[Andrew]

I am trying to format the contents of a cell to display
the date in the format: yyyy/mm/dd

Right now in the code I have:

OutputSheet.Cells(OutputRowCounter - NumTrans - 2,
5).Value = Format(Input1Sheet.Cells(2,
4).Value, "yyyy/mm/dd")

What ends up happening is that it formats the variable
properly, but when it writes it to the cell Excel changes
the date back to mm/dd/yyyy.

How do I get my change to stick?

 

[J.E. McGimpsey]

XL will still parse whatever string VBA hands it (just as if you
typed it in), then display the value per the cell's format, so
Format() won't be effective.

One way:

With OutputSheet.Cells(OutputRowCounter - NumTrans - 2, 5)
.Value = Input1Sheet.Cells(2, 4).Value
.NumberFormat = "yyyy/mm/dd"
End With