countifs with date in vba code

[jb]

>>> You've noted many times <<<
>>> use code - tags <<<​

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?

 

[Monty]

>>> You've noted many times <<<
>>> use code - tags <<<​

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

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

 

[jb]

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

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.

 

[Monty]

Sir, I incorporated your code but it is still giving 0.
reference file is attached here.

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.

 

[p45cal]

ws.Cells(i, 10).Value = Application.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))

 

[Debaser]

Use:

ws.Cells(i, 10).Value = Application.WorksheetFunction.CountIfs(rng, ">=" & CLng(startdate), rng, "<=" & CLng(endDate))

 

[jb]

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.

 

[Monty]

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.

 

[Monty]

=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"), ""), "")

 

[vletm]

@Monty
Focus, the title is ...

 

[Monty]

Not sure, what i was doing did wrongly posted as reply...Apologies