Counting dates with specific criteria

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,
 
T

T. Valko

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top