Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    comparing dates (Excel 2000)

    I'm having the most ridiculous time trying to compare a date in a cell with the output from Now(). This should be easy! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    I have a cell formatted as Date (dd-mm-yyyy) and a constant ExpDate (expiration date) defined as Date = "#1/1/1900#" When I click on the cell containing the date it shows in the formula bar as "1/1/1900". So far so good.

    I have code that compares the output of Now() with the date in this cell but it always comes up as 12/31/1899 not 1/1/1900.<pre>
    ' fetch date in the cell (1/1/1900)
    dt = rng.offset(0,4)
    ' dt returns 12/31/1899 not 1/1/1900

    if Now() <> dt then ' if current date <> my expired date
    ' do something
    end if
    </pre>


    Even if I use Format() to format the date better, it still doesn't work. What's the mystery behind comparing dates?
    Deb <img src=/S/smash.gif border=0 alt=smash width=30 height=26>]

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

    Re: comparing dates (Excel 2000)

    In the first place, Excel dates are not correct before March 1, 1900. This is a deliberate bug, or feature. Lotus 123, which used to be the "standard" spreadsheet program in the 1980's, incorrectly counted 1900 as a leap year. For compatibility reasons, Microsoft copied this in Excel.
    VBA, however, doesn't count 1900 as a leap year. So dates before March 1, 1900 are different in Excel and VBA.

    In the second place, Now() returns the current date and time. If you want to compare a date to the current date, use the Date() function. This returns the date part only, not the time.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates (Excel 2000)

    Ah, ok, I'll pick a more recent date as my choice for 'no expiration'. Actually I am using just the 'date' portion of Now() since I did the Format() call to return the date portion which matched the date in the cell. I'll try it again using a newer date and also with the Date() function too.

    Thnx, Deb <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates (Excel 2000)

    Using the Format function to return the date is going to return a string. If you don't then convert this back to a date, then you are likely to have problems when you try to compare to a cell which contains a date, particularly if you try to do anything other that equal to comparisons. You could also have problems with equal to if the cell is not converted to a string the same way that the Format function does the conversion. You would be better off using the Date function and comparing date values.
    Legare Coleman

Posting Permissions

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