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?

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

In article ,
"Andrew" wrote:

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?