Excel 2016: VBA Countifs Date Issue

[ajay_gajree]

Hi All

I am using the following formula to calculate the last quarter end date

x = DateSerial(Year(Date), Int((Month(Date) - 4) / 3) * 3 + 4, 0)

This currently returns 30/06/2018

When try to use this in the below formula I am not getting the correct value. I have taken the other conditions out as when I tested them they were fine.

y = Application.WorksheetFunction.CountIfs(Worksheets("Sheet 1").Range("tblDatabase[[Quarter]]"), x)

In the worksheet the date column is formatted MMM-YY
When you look at a cell the value is shown as DD/MM/YYY as required

Any help appreciated!

 

[steve the fish]

So in your Quarter column do you just have dates that represent the last day of the quarter??

 

[ajay_gajree]

Yes that is correct