locked
Displaying a date without excel messing with it RRS feed

  • 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");

    Thursday, October 21, 2010 8:58 PM

Answers

  • Using VBA:

     

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

     


    GSNU30001
    Thursday, October 21, 2010 9:40 PM

All replies

  • Using VBA:

     

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

     


    GSNU30001
    Thursday, October 21, 2010 9:40 PM
  • 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

    Thursday, October 21, 2010 10:51 PM