My Simple IF STATEMENT to check for a date within a date range doesn't work properly. Please help!

[tdguy09]

Hi All,

I am trying to write some code that runs through a column in excel and checks if the date within each row in that column falls between two dates that are entered by the user as start date and end date. Unfortunately, it is not working!!


Here is the code:

For i = 3 To numrows
        
                  
            If (ActiveSheet.Range("H" & i) >= DStartDate) And (ActiveSheet.Range("H" & i) <= DEndDate) Then
                 totNumFound = totNumFound + 1
            
          End If
        Next

Could someone please help me. It seems straightforward but somehow it doesnt quite work. PROBLEM EXAMPLE:

If DStartdate = 08/30/2012 and DEndDate = 08/31/2012 then it seems to only count all records where date aka ActiveSheet.Range("H" & i) = 08/30/2012 but it ignores all dates that = 08/31/2012.

Totnumfound is being displayed on the sheet but is used in multiple loops through multiple arrays to generate multiple reports on a number of sheets. I'm adding this If statement to evaluate on date so that I can run the report for a particular date period instead of a 'snapshot' showing all records.

The If statement does not fail but does not evaluate properly. The >= and <= should resolve to "between dateA and dateB" but for some reason excludes the records <= dateB.
I just need someone to help me figure out why this is happening or alternatively correct the statement to work properly.
​

PLEASE HELP!​

I'm down to my last few hairs!! Please help.



THANKS!!!

 

[AlphaFrog]

The bit of code provided seems OK. A couple of things to consider.
Is numrows the number of rows you expect?
How are you defining DStartDate and DEndDate?
Are the dates in column H Serial-dates or Text-dates?

 

[tdguy09]

The bit of code provided seems OK. A couple of things to consider.
Is numrows the number of rows you expect?
How are you defining DStartDate and DEndDate?
Are the dates in column H Serial-dates or Text-dates?

Hi. Thanks for your reply. numrows is the count to the last populated row. DStartDate and DEndDate are defined as date i.e. Dim DStartDate as Date. I don't know the answer to your last question but what is in colum h are dates copied directly from Outlook (recieved date) which is in the following format 7/30/12 10:29:00 AM. The cells are also formatted accordingly.

 

[par60056]

make sure that your end date is 8/31/2012 11:59:59pm

 

[AlphaFrog]

Column H has Date-time values. The End Date is just a date with no time and would be considered 8/31/2012 12:00 AM

Add one day to the End Date and use just the < operator.
< 9/1/2012

 

[tdguy09]

Column H has Date-time values. The End Date is just a date with no time and would be considered 8/31/2012 12:00 AM

Add one day to the End Date and use just the < operator.
< 9/1/2012

Thanks again. I will investigate this. Will let you know how it turns out.

 

[tdguy09]

I managed to get it to work. Since I was concerned about 'manipulating' the criteria by adding a day to the end of the day as you suggested, I tried to incorporate the 'time' piece of the whole 'datetime'. I declared a starttime and endtime variable and assigned them the static values of 12:00:00 AM and 11:59:59 respectively. So I now had four separate variables for start date/time and end date/time all declared as date. I then tried to combine them into a single variable for start SDATETIME and end ENDDATETIME which I used in my IF Statement. This kept failing however though I tried many variation and also using CDATE. Finally it struck me that I should declare all the variables as string and then use CDATE in the IF Statement to convert the combined date/time string to a date. That finally worked.

I took both suggestions into consideration (just adding another day OR entering the full datetime string into the input box) but in the end I wanted it to evaluate on the precise time and not have the user have to enter in the entire date/time string.

Thanks for your input though. It set me on the right path. The code is below in case anyone is interested.

Global DStartDate As String
Global StartTime As String
Global DEndDate As String
Global EndTime As String
Global SDateTime As String
Global EDateTime As String
Global NoDate as Boolean

Private Sub UpdateSummarySheet_Click()
Sheets("Lists").Visible = True
nodate = False
StartTime = "12:00:00 AM"
EndTime = "11:59:59 PM"
'On Error GoTo ErrHandler
DStartDate = InputBox("Enter Start Date", "Valid Format - mm/dd/yyyy")
DEndDate = InputBox("Enter End Date", "Valid Format - mm/dd/yyyy")
SDateTime = DStartDate & " " & StartTime
EDateTime = DEndDate & " " & EndTime
GetCLOSED
ExitHandler:
Exit Sub
ErrHandler:
nodate = True
GetCLOSED
Resume ExitHandler
End Sub


Sub CountCLOSED(strCounter)
Dim i As Integer
Dim totNumFound As Integer
Dim numrows As Integer

Sheets("Service Request Detail").Select
totNumFound = 0
numrows = 0
numrows = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

If nodate = False Then

For i = 3 To numrows
If (ActiveSheet.Range("BC" & i).Value = strCounter) Then
If (ActiveSheet.Range("H" & i) >= CDate(SDateTime)) And (ActiveSheet.Range("H" & i) <= CDate(EDateTime)) Then

totNumFound = totNumFound + 1
End If
End If
Next

Else

For i = 3 To numrows
If (ActiveSheet.Range("BC" & i).Value = strCounter) Then
totNumFound = totNumFound + 1
End If

Next

End If
'Print to Summary Sheet
Sheets("HPP Stat Summary").Range("O" & summRow).Value = totNumFound
Sheets("HPP Stat Summary").Select

End Sub