Results 1 to 6 of 6

Thread: Check if Date is greater than todays date

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Check if Date is greater than todays date

    Hello:

    VBA Excel (very old school insanity).

    I have this very simple line:

    Code:
            If (Format(rs.Fields("ShipDate"), "yyyy-mm-dd") > Format(Now(), "yyyy-mm-dd")) = True Then
    On these dates:
    Code:
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>2020-08-20 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2021-02-22 > 2020-10-21
    ===>2020-09-28 > 2020-10-21
    ===>2020-09-28 > 2020-10-21
    ===>2020-11-16 > 2020-10-21
    ===>2020-11-16 > 2020-10-21
    ===>2020-11-16 > 2020-10-21
    ===>2020-11-16 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2021-03-22 > 2020-10-21
    ===>2020-09-11 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2021-01-25 > 2020-10-21
    ===>2020-11-09 > 2020-10-21
    ===>1979-01-01 > 2020-10-21
    ===>2020-11-06 > 2020-10-21
    Some of the dates are in fact greater than today's date, but I get nothing. If I change it to less than, I get everything, and some of those dates are greater than todays date.

    Thanks!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Check if Date is greater than todays date

    what happens when you change it to this:
    Code:
    If rs.Fields("ShipDate") > Now() Then
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Check if Date is greater than todays date

    Code:
    If CStr((Format(rs.Fields("ShipDate"), "yyyy-mm-dd")) > CStr(Format(Now(), "yyyy-mm-dd"))) = True Then
    Or

    Code:
    Option Explicit
    
    
    Sub CompareDateFields()
    
        Dim strTemp1 As String, strTemp2 As String
        Dim intTemp1 As Integer
        
        For intTemp1 = 1 To 74
            strTemp1 = CStr(Sheet1.Cells(intTemp1, 1))
            strTemp2 = CStr(Sheet1.Cells(intTemp1, 2))
            ' Stop
            If strTemp1 > strTemp2 Then
                Sheet1.Cells(intTemp1, 3) = "True"
            Else
                Sheet1.Cells(intTemp1, 3) = "False"
            End If
        Next intTemp1
    
    End Sub
    The above macro works on a sheet with your dates in rows 1-72, columns 1-2

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Check if Date is greater than todays date

    That's about where I started.

    It isn't reading the dates because it puts everything in...
    To elaborate further...

    Code:
        Dim i As Integer
        i = 3
            
        Do While Not rs.EOF
            Debug.Print ("===>" & rs.Fields("ShipDate") & " > " & Format(Now(), "yyyy-mm-dd"))
            If rs.Fields("ShipDate") > Now() Then
                ThisWorkbook.ActiveSheet.Cells(i, 1).Value = (rs.Fields("ShipTo"))
                ThisWorkbook.ActiveSheet.Cells(i, 2).Value = (rs.Fields("Product"))
                ThisWorkbook.ActiveSheet.Cells(i, 3).Value = (rs.Fields("JobNo"))
                ThisWorkbook.ActiveSheet.Cells(i, 4).Value = (rs.Fields("Detail"))
                ThisWorkbook.ActiveSheet.Cells(i, 5).Value = (rs.Fields("Feet"))
                ThisWorkbook.ActiveSheet.Cells(i, 7).Value = (rs.Fields("Designer"))
                ThisWorkbook.ActiveSheet.Cells(i, 8).Value = (rs.Fields("EstHrs"))
    
                If (rs.Fields("Started")) <> #1/1/1979# Then
                    ThisWorkbook.ActiveSheet.Cells(i, 9).Value = (rs.Fields("Started"))
                
                Else
                    ThisWorkbook.ActiveSheet.Cells(i, 9).Value = ""
            
                End If
            
                If (rs.Fields("Completed")) <> #1/1/1979# Then
                    ThisWorkbook.ActiveSheet.Cells(i, 10).Value = (rs.Fields("Completed"))
    
                Else
                    ThisWorkbook.ActiveSheet.Cells(i, 10).Value = ""
            
                End If
            
                If (rs.Fields("ShipDate")) <> #1/1/1979# Then
                    ThisWorkbook.ActiveSheet.Cells(i, 13).Value = (rs.Fields("ShipDate"))
                
                Else
                    ThisWorkbook.ActiveSheet.Cells(i, 13).Value = ""
            
                End If
            
            End If
            
            rs.MoveNext
            
            i = i + 1
    
        Loop
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Check if Date is greater than todays date

    Regarding your code line in post #1, who knows what data types the 2 expressions will be. If you actually tried the code I posted, your issue would probably be solved. That codes compares a String against another String, so we know the data types, and it works.

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Check if Date is greater than todays date

    You really should not try to compare dates as strings. Compare them as dates and you will get the desired results.

Posting Permissions

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



Click Here to Expand Forum to Full Width