• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

countifs with date in vba code

jb

Member
>>> You've noted many times <<<
>>> use code - tags <<<
Code:
Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("n6:n65")
       
 For i = 6 To lastrow
            If Cells(i, 4) <> "" Then
                    startdate = Cells(i, 7)
                    endDate = Cells(i, 8)
                    Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate)
            End If
Next i

Above is partly vba code where I have used countifs with date columns.
List of dates are available in in n6 to n65. (May not be all the cells in this range will have dates. But dates will start from n6 cell)
Column g (column no. 7) has start date. Column h (column no. 8) has end date.

There will be multiple rows having different value of start and end date. Data starts from row number 6. Date format is dd-mm-yyyy.

Now I want to calculate number of dates available in n2:n65 which fall between start date and end date in each row.
I want this calculated number in column number 10 (column j).

When I run this macro, it calculates 0. It is not giving any error. Where I am wrong in formula?
 
Last edited by a moderator:
>>> You've noted many times <<<
>>> use code - tags <<<
Code:
Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("n6:n65")
      
 For i = 6 To lastrow
            If Cells(i, 4) <> "" Then
                    startdate = Cells(i, 7)
                    endDate = Cells(i, 8)
                    Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate)
            End If
Next i

Above is partly vba code where I have used countifs with date columns.
List of dates are available in in n6 to n65. (May not be all the cells in this range will have dates. But dates will start from n6 cell)
Column g (column no. 7) has start date. Column h (column no. 8) has end date.

There will be multiple rows having different value of start and end date. Data starts from row number 6. Date format is dd-mm-yyyy.

Now I want to calculate number of dates available in n2:n65 which fall between start date and end date in each row.
I want this calculated number in column number 10 (column j).

When I run this macro, it calculates 0. It is not giving any error. Where I am wrong in formula?

Thanks for the partial code...please find below modified version

Code:
Dim ws As Worksheet
Dim lastrow As Long
Dim rng As Range
Dim startdate As Date
Dim endDate As Date

Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("n6:n65")

For i = 6 To lastrow
    If ws.Cells(i, 4) <> "" Then
        startdate = ws.Cells(i, 7).Value
        endDate = ws.Cells(i, 8).Value
        ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate)
    End If
Next i
 
Thanks for the partial code...please find below modified version

Code:
Dim ws As Worksheet
Dim lastrow As Long
Dim rng As Range
Dim startdate As Date
Dim endDate As Date

Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("n6:n65")

For i = 6 To lastrow
    If ws.Cells(i, 4) <> "" Then
        startdate = ws.Cells(i, 7).Value
        endDate = ws.Cells(i, 8).Value
        ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate)
    End If
Next i
Sir, I incorporated your code but it is still giving 0.
reference file is attached here.
 

Attachments

  • Activity Leave Report Even Sem 23-24.xlsm
    315.4 KB · Views: 4
Sir, I incorporated your code but it is still giving 0.
reference file is attached here.
Code:
Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("N6:N65")
      
For i = 6 To lastrow
    If ws.Cells(i, 4).Value <> "" Then
        startDate = ws.Cells(i, 7).Value
        endDate = ws.Cells(i, 8).Value
        ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startDate, rng, "<=" & endDate)
    End If
Next i



Make sure to include .Value when checking if Cells(i, 4) is not empty. Also, ensure that your date formats in columns N, G, and H are consistent (e.g., dd-mm-yyyy) for accurate comparisons. If the issue persists, please double-check your data and provide more information about the structure of your worksheet or any specific error messages you encounter.
 
  • Like
Reactions: jb
Code:
ws.Cells(i, 10).Value = Application.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))
 
  • Like
Reactions: jb
Use:

Code:
ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))
 
  • Like
Reactions: jb
Code:
Set ws = ActiveWorkbook.Sheets("Consolidated")
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("N6:N65")
    
For i = 6 To lastrow
    If ws.Cells(i, 4).Value <> "" Then
        startDate = ws.Cells(i, 7).Value
        endDate = ws.Cells(i, 8).Value
        ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & startDate, rng, "<=" & endDate)
    End If
Next i



Make sure to include .Value when checking if Cells(i, 4) is not empty. Also, ensure that your date formats in columns N, G, and H are consistent (e.g., dd-mm-yyyy) for accurate comparisons. If the issue persists, please double-check your data and provide more information about the structure of your worksheet or any specific error messages you encounter.
ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))

changing this line solved my problem as suggested by p45cal and Debaser.

Monty sir, I was worried because my data formats were double checked. I incorporated changes given by you but still it was not calculating days. But changing one above line did miracle.

Thank you all.
 
Last edited by a moderator:
It's great to hear that you were able to make the necessary adjustments to the VBA code, and that it's working as expected now. If you have any more questions or need further assistance with VBA or any other Excel-related tasks, feel free to ask!
ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))

changing this line solved my problem as suggested by p45cal and Debaser.

Monty sir, I was worried because my data formats were double checked. I incorporated changes given by you but still it was not calculating days. But changing one above line did miracle.

Thank you all.
 
=IFERROR(IF(AND(VLOOKUP(L2, Source!A:D, 4, FALSE) = H2, L2 <> ""), TEXT(IFERROR(IFERROR(VLOOKUP(L2, Source!A:J, 9, FALSE), VLOOKUP(L2, Source!A:J, 10, FALSE)), "MM-DD-YYYY"), ""), "")
 
Last edited:
Back
Top