+ Reply to Thread
Results 1 to 4 of 4

Comparing Dates - Flawed Code?

  1. #1
    Registered User
    Join Date
    09-22-2005
    Posts
    8

    Comparing Dates - Flawed Code?

    Afternoon all,

    I have a macro that compares dates but no matter what i try it doesn't seem to work.

    Simply it goes a little like:

    Dim cellDate 'Date that is in a certain cell
    'format DD/MM/YYYY HH:MM.

    cellDate = Cells(row, col) 'Varies depending on previous actions.

    Dim formatDate 'cellDate formatted
    'into same as i want to compare it to.

    formatDate = Format(cellDate, "dd/mm/yyyy")

    If formatDate >= "01/10/2005" Then

    If formatDate <= "31/10/2005" Then

    End If

    End If

    I have tried initialising the variable as dates, strings, variants, but none of the comparing IF statements work???

    Any Help?

    Thanks in advance!

  2. #2
    K Dales
    Guest

    RE: Comparing Dates - Flawed Code?

    The problem is comparing the dates - now strings - with < and > comparisons.
    What you now get is an alphabetic comparison of the strings, not a date-wise
    comparison. For example, alphabetically the string "2" comes AFTER "10"
    (just as "b" comes after "az").

    You can do the formatting if you still need it for other purposes, but then
    do the comparison as:
    If DateValue(formatDate) >= DateValue("01/10/2005") Then...
    If DateValue(formatDate) <= DateValue("31/10/2005") Then...
    This explicitly converts them back to date values for the purposes of the
    comparison.
    --
    - K Dales


    "br_turnbull" wrote:

    >
    > Afternoon all,
    >
    > I have a macro that compares dates but no matter what i try it doesn't
    > seem to work.
    >
    > Simply it goes a little like:
    >
    > Dim cellDate 'Date that is in a certain cell
    > 'format DD/MM/YYYY HH:MM.
    >
    > cellDate = Cells(row, col) 'Varies depending on previous actions.
    >
    > Dim formatDate 'cellDate formatted
    > 'into same as i want to compare it to.
    >
    > formatDate = Format(cellDate, "dd/mm/yyyy")
    >
    > If formatDate >= "01/10/2005" Then
    >
    > If formatDate <= "31/10/2005" Then
    >
    > End If
    >
    > End If
    >
    > I have tried initialising the variable as dates, strings, variants, but
    > none of the comparing IF statements work???
    >
    > Any Help?
    >
    > Thanks in advance!
    >
    >
    > --
    > br_turnbull
    > ------------------------------------------------------------------------
    > br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
    > View this thread: http://www.excelforum.com/showthread...hreadid=478200
    >
    >


  3. #3
    Registered User
    Join Date
    09-22-2005
    Posts
    8
    Genius, thanks a lot, A+

  4. #4
    Dave Peterson
    Guest

    Re: Comparing Dates - Flawed Code?

    dim CellDate as Date
    celldate = int(Cells(row, col).value) 'remove the time

    'dateserial(year,month,day)
    if celldate >= dateserial(2005,1,10) then
    '....
    if celldate <= dateserial(2005,10,31) then


    Comparing strings (the date formatted) can cause lots of trouble. Comparing the
    actual date seems to work ok.

    br_turnbull wrote:
    >
    > Afternoon all,
    >
    > I have a macro that compares dates but no matter what i try it doesn't
    > seem to work.
    >
    > Simply it goes a little like:
    >
    > Dim cellDate 'Date that is in a certain cell
    > 'format DD/MM/YYYY HH:MM.
    >
    > cellDate = Cells(row, col) 'Varies depending on previous actions.
    >
    > Dim formatDate 'cellDate formatted
    > 'into same as i want to compare it to.
    >
    > formatDate = Format(cellDate, "dd/mm/yyyy")
    >
    > If formatDate >= "01/10/2005" Then
    >
    > If formatDate <= "31/10/2005" Then
    >
    > End If
    >
    > End If
    >
    > I have tried initialising the variable as dates, strings, variants, but
    > none of the comparing IF statements work???
    >
    > Any Help?
    >
    > Thanks in advance!
    >
    > --
    > br_turnbull
    > ------------------------------------------------------------------------
    > br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
    > View this thread: http://www.excelforum.com/showthread...hreadid=478200


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1