Counting dates with specific criteria
 

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

The criteria a 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,
--
Steve

Counting dates with specific criteria
 

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))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi, I have a column with various dates, I need some formulae to count
different criteria.

The criteria a 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,
--
Steve