Calculate number of dates within a column based on Month

**jpruffle** · 05-15-2009, 05:42 AM

Hi,

I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.

So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.

Also is this possible with the month ahead based on the same principle?

Thanks.

**zbor** · 05-15-2009, 05:52 AM

try this:

=SUMPRODUCT(--(A1:A33>TODAY());--(A1:A33<DATE(YEAR(TODAY());1+MONTH(TODAY());1)))

If you need to calulcate curent date too put >= in first formula.
Also change ; to , if needed and extend range

**jpruffle** · 05-15-2009, 06:01 AM

That's great.

What about for the month after?

Also can you help me with the formula to calculate the total number of dates (events) within the month as a total?

Thanks again for your help.

**zbor** · 05-15-2009, 06:15 AM

For one more month change nukber from 1 to 2
=SUMPRODUCT(--(A1:A33>TODAY());--(A1:A33<DATE(YEAR(TODAY());2+MONTH(TODAY());1)))

If you need more than that you could consider of adding one more cell (E.g. C1) where you would write some date (e.g. 14/7/2009) or create drop down list with first dates of months. Then you can use:

=SUMPRODUCT(--(A1:A33>TODAY());--(A1:A33<C1))

And for current month

=SUMPRODUCT(--(A1:A33>DATE(YEAR(TODAY());MONTH(TODAY());0));--(A1:A33<DATE(YEAR(TODAY());1+MONTH(TODAY());1)))

**jpruffle** · 05-15-2009, 08:11 AM

Thanks again for the reply.

The formula:

Please Login or Register  to view this content.

Also adds dates that are greater than today? Can it just add dates starting from the 1st of the next month?

Thanks

**zbor** · 05-15-2009, 08:38 AM

(A1:A33>DATE(YEAR(TODAY());1+MONTH(TODAY());0));

Formula is the same in all cases.. You only need to adjust months.

So MONTH(TODAY()) will give you current month (5).
If you need montrh or two after just add +1 or +2
or -1 for previous (X-1 is same as -1+X)

Year (TODAY()) just give you current year but you can write 2009 instead