Excel VBA Countif between dates
countif, date, excel, function, vba
Solution
For those who may come across this and are interested, the solution is to add Cdbl and Cdate functions around the Date1 and Date 2 Variables (changed to Newdate and AccountDate variables for clarity as per the below). Works like a charm. Working with date formats in VBA can be a pain!
Function LastWrapUp(AccountID, AccountType, AccountDate As Date)
'Find if current WrapUp is the last for a given account number within a 3 month period.
'need to include reference to specific sheets
Dim NewDate As Date
Dim LastRow As Long
NewDate = DateAdd("M", 3, AccountDate)
LastRow = Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A" & Rows.Count).End(xlUp).Row
If AccountType = "Dummy ID" Then
LastWrapUp = "Dummy ID"
ElseIf Application.WorksheetFunction.CountIfs(Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), ">=" & CDbl(CDate(AccountDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), "<" & CDbl(CDate(NewDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("L2:L" & LastRow), AccountID) > 1 Then
LastWrapUp = "Not Final Wrap Up"
ElseIf Application.WorksheetFunction.CountIfs(Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), ">=" & CDbl(CDate(AccountDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("A2:A" & LastRow), "<" & CDbl(CDate(NewDate)), Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range("L2:L" & LastRow), AccountID) = 1 Then
LastWrapUp = Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data").Range(AccountID.Address).Offset(0, -4)
Else
LastWrapUp = "Error"
End If
End Function
Problem
I am needing to create a function to count the occurrences of an account number between two dates. The first date is based on the function input and the second is 3 months in advance (date may not be contained within the data set). Date values in the range are in the format "dd/mm/yyyy h:mm". Due to the size of the dataset approx 150,000 lines i am wanting to perform this in the code and not paste or evaluate the COUNTIF formula within a specified cell. The worksheet function works when only the AccountNo variable is referenced but not when the conditional ">=" or "<=" Date variables are added e.g. Application.WorksheetFunction.CountIfs(Range("L2:L" & Endrow), AccountNo) > 1 Then ''''(Works) The function needs to return a result based on the countif result as below. Thanks, ``` Function LastWrapUp(Date1 As Date, AccountNo) Dim Date2 As Date Dim Endrow As Long Date2 = DateAdd("M", 3, Date1) Endrow = Range("A" & Rows.Count).End(xlUp).Row If Application.WorksheetFunction.CountIfs(Range("A2:A17643"), ">=" & Date1, Range("A2:A" & Endrow), "<" & Date2, Range("L2:L" & Endrow), AccountNo) > 1 Then LastWrapUp = "Not Final Wrap Up" ElseIf Application.WorksheetFunction.CountIfs(Range("A2:A" & Endrow), ">=" & Date1, Range("A2:A" & Endrow), "<" & Date2, Range("L2:L" & Endrow), AccountNo) = 1 Then LastWrapUp = "Yes" Else LastWrapUp = "Error" End If Debug.Print LastWrapUp End Function ```