Counting dates with specific criteria

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi, I have a column with various dates, I need some formulae to count
different criteria.

The criteria are: 1. Dates in the current month. 2. Dates last month. 3.
dates in a calender year. 4. Dates in a financial year.
Each criteria will be shown in seperate cells on a different worksheet on
Excel 2003, also the current month is always the current month as we progress
through the year.
Thanks in advance,
 
Try these...

1. Dates in the current month.

=SUMPRODUCT(--(TEXT(A2:A21,"myyyy")=TEXT(NOW(),"myyyy")))

2. Dates last month.

=SUMPRODUCT(--(TEXT(A2:A21,"myyyy")=TEXT(TODAY()-DAY(NOW()),"myyyy")))

3. Dates in a calender year. Where n = the year number

=SUMPRODUCT(--(YEAR(A2:A21)=n))

4. Dates in a financial year.

C2 = the start date of your financial year
D2 = the end date of your financial year

=SUMPRODUCT(--(A2:A21>=C2),--(A2:A21<=D2))
 
Back
Top