Displaying a date without excel messing with it

Question

I used to know how to display a date anyway I wanted. Forget how to do it now. Excel, when it sees a date tries to format it and displays it like mm/dd/yy ... not interested in their choice.

 

I've temporarily gotten around the problem but putting a space in front of the date to trick Excel.

 

Since I used to know how to do it properly I'll post the answer if and when I ever find a sample of my code where I did this before. I seem to remember "OLE" being in something somewhere.

 

 

Current code looks a bit like this:

rg.Value2 = (char)32M + x.date.ToString("yyyy-MM-dd");

Answer 1

Using VBA:

 

Sub demo()
Dim rg As Range
Set rg = Range("A1")
rg.NumberFormat = "yyyy-mm-dd"
rg.Value = Date
End Sub

 

---

GSNU30001

Answer 2

Gary's student got the answer. So the final code would look like this:

rg.Value2 = x.date.ToString("yyyy-MM-dd"); ' no need for the space
rg.NumberFormat = "yyyy-mm-dd"

Thanks!
Sanjay Valiyaveettil