Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Serial Values for Dates (2000 SP3)

    I was wondering if there was a function or some way to convert a date to a serial value like in excel.
    example
    1/1/1900 = 1
    4/1/2006 = 38808
    4/2/2006 = 38809

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Serial Values for Dates (2000 SP3)

    Use the CLng function.
    In a query: CLng([DateField])
    In the control source of a text box: =CLng([DateField])
    (1/1/1900 corresponds to 2, by the way. Excel contains a deliberate error in dates before March 1, 1900)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Serial Values for Dates (2000 SP3)

    Thank You and it is good to know that excel contains that deliberate error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Serial Values for Dates (2000 SP3)

    The error in Excel was introduced for compatibility with Lotus 1-2-3, which was the market leader in spreadsheets in the 1980's. The 1-2-3 programmers hadn't taken into account that 1900 was not a leap year, so dates before March 1, 1900 are off by 1. In order to win over Lotus customers, the Excel programmers wanted Excel to be able to open and edit Lotus 1-2-3 spreadsheets without conversion, so they did the same. VBA, however, uses the "official" calendar rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •