none
Date literals RRS feed

  • Question

  • Hello all,

    I am trying to set a Date variable with the 30th of December, 1899. Here's what I am doing:

    Dim tstDate As Date
    tstDate = #12/30/1899#
    

    but Excel's VBA editor automatically converts the assignment to:

    tstDate = #12:00:00 AM#

    as soon as I remove the cursor from the line.

    The funny thing is that it does not happen with other dates. For example,

    tstDate = #12/31/1899#
    tstDate = #12/29/1899#
    

    work as expected.

    Could you help me with this?

    Anton

    EDIT: I am using Excel 2007.


    Wednesday, November 9, 2011 2:07 PM

Answers

  • VBA is trying to "help" you.  Remember that the Date data type includes both dates and times.  VBA assumes that if the integer part is 0, you are trying to manipulate times.
    gsnu201111
    • Marked as answer by Anton Shepelev Thursday, November 10, 2011 1:59 PM
    Wednesday, November 9, 2011 4:41 PM
    Moderator
  • I would simply use this, since docDate is a date value:

    Range(fDate).Value = docDate

    If you also need to format the cell as a date:

    With Range(fDate)
        .Value = docDate
        .NumberFormat = "mmmm d, yyyy"
    End With


    HTH, Bernie
    • Marked as answer by Anton Shepelev Thursday, November 10, 2011 1:58 PM
    Thursday, November 10, 2011 1:36 PM

All replies

  • That is because 12/30/1899 has the value of 0 (in VBA)
    gsnu201111
    Wednesday, November 9, 2011 3:31 PM
    Moderator
  • That is because 12/30/1899 has the value of 0 (in VBA)

    It means zero days. But why should VBA dislike such a date literal?
    • Marked as answer by Anton Shepelev Thursday, November 10, 2011 1:58 PM
    • Unmarked as answer by Anton Shepelev Thursday, November 10, 2011 1:59 PM
    Wednesday, November 9, 2011 3:56 PM
  • VBA is trying to "help" you.  Remember that the Date data type includes both dates and times.  VBA assumes that if the integer part is 0, you are trying to manipulate times.
    gsnu201111
    • Marked as answer by Anton Shepelev Thursday, November 10, 2011 1:59 PM
    Wednesday, November 9, 2011 4:41 PM
    Moderator
  • If you want to keep the string 12/30/1899 in your code for documentation, you could use

    tstDate = DateValue("12/31/1899")


    HTH, Bernie
    Wednesday, November 9, 2011 4:54 PM
  • Thank you, Gary's student and Bernie.

    Bernie, no I don't really need to keep that very string and only want to have a DATE variable contain that value. Is there any other way to do it? Is the solution you suggested portable across different localizations of Excel and Windows?

    Anton


    EDIT:

    Right now I am using this code to set an Excel cell to a ceratin date in a portable way:

    Dim docDate As Date
    ' Here, load docDate from data base
    Names(fDate).RefersToRange.Value = DateDiff("d", 0, docDate)

    Can it be done in a cleaner way?

     

    Thursday, November 10, 2011 8:37 AM
  • The following illustrates placing a date in a cell.  You can either use a VBA string variable or a VBA Date variable.  Both A1 & A2 end up with the same date value:

     

    Sub DatePlacer()
    Dim s As String, d As Date
    s = "1/18/1945"
    d = DateValue(s)
    [A1] = s
    [A2] = d
    End Sub


    gsnu201111
    Thursday, November 10, 2011 10:35 AM
    Moderator
  • I would simply use this, since docDate is a date value:

    Range(fDate).Value = docDate

    If you also need to format the cell as a date:

    With Range(fDate)
        .Value = docDate
        .NumberFormat = "mmmm d, yyyy"
    End With


    HTH, Bernie
    • Marked as answer by Anton Shepelev Thursday, November 10, 2011 1:58 PM
    Thursday, November 10, 2011 1:36 PM
  • OK, I get it. Thank you, guys.

    Thursday, November 10, 2011 1:58 PM