SUMPRODUCT Date Ranges

[Nick]

Hi,

I need to note a '0' or '1' within each of the monthly cells in a
table to show resource secured within a given month (the date ranging
from the start and finish date).

So, if the START date is A1 and the END date is B1 and I have a cell
for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.

As an example, the start date is 06/02/10 and the end date is
08/08/10, how can I show "1" against the Feb to August cells and all
the other cells where they don't fall into the month as "0", i.e. Jan
as "0"?

Many thanks in advance.

 

[Bob Phillips]

Try

=--(NOT(OR((DATEVALUE("01-"&C1&"-"&YEAR($A$1))<$A$1-DAY($A$1)+1),(DATEVALUE("01-"&C1&"-"&YEAR($B$1))>$B$1-DAY($B$1)+1))))

 

[Joe User]

So, if the START date is A1 and the END date is B1 and
I have a cell for each month, i.e. C1 = Jan, D1 = Feb
and so on till Dec.

As an example, the start date is 06/02/10 and the end
date is 08/08/10, how can I show "1" against the Feb to
August cells and all the other cells where they don't
fall into the month as "0", i.e. Jan as "0"?

Write the following formula in C1 and copy across through N1:

=--AND(MONTH($A1)<=COLUMN()-2, COLUMN()-2<=MONTH($B1))


----- original message -----

 

[Joe User]

PS....

Write the following formula in C1 and copy across through N1:
=--AND(MONTH($A1)<=COLUMN()-2, COLUMN()-2<=MONTH($B1))

I would prefer to use row 1 as a title row, putting the months into C1:N1.
Then use row 2 for the purpose you described, to wit: A2 and B2 have the
start and end dates, and C2:N2 contains 1s and 0s.

Put the dates 1/1/2010, 1/2/2010 etc (d/m/yyyy form) into C1:N1, then format
C1:N1 with Custom mmm; the year and day do not matter. Then put the
following formula into C2 and copy across through N2:

=--AND(MONTH($A2)<=MONTH(C$1),MONTH(C$1)<=MONTH($B2))

Subject: SUMPRODUCT Date Ranges

I keep wondering why Nick specified SUMPRODUCT in the subject line. For the
problem he describes, SUMPRODUCT is not needed. But I wonder if his
description is intended to be a paradigm, and he is really asking how to do
effectively AND using SUMPRODUCT.

In that case, to implement the same logic as above, you might write:

=SUMPRODUCT((MONTH($A2)<=MONTH(C$1))*(MONTH(C$1)<=MONTH($B2)))

Again, this is totally unnecessary, even inappropriate, for the immediate
problem. But it might answer the tacit question about how to do similar
logic using SUMPRODUCT.


----- original message ------

 

[Nick]

PS....


I would prefer to use row 1 as a title row, putting the months into C1:N1..
Then use row 2 for the purpose you described, to wit:  A2 and B2 have the
start and end dates, and C2:N2 contains 1s and 0s.

Put the dates 1/1/2010, 1/2/2010 etc (d/m/yyyy form) into C1:N1, then format
C1:N1 with Custom mmm; the year and day do not matter.  Then put the
following formula into C2 and copy across through N2:

=--AND(MONTH($A2)<=MONTH(C$1),MONTH(C$1)<=MONTH($B2))



I keep wondering why Nick specified SUMPRODUCT in the subject line.  For the
problem he describes, SUMPRODUCT is not needed.  But I wonder if his
description is intended to be a paradigm, and he is really asking how to do
effectively AND using SUMPRODUCT.

In that case, to implement the same logic as above, you might write:

=SUMPRODUCT((MONTH($A2)<=MONTH(C$1))*(MONTH(C$1)<=MONTH($B2)))

Again, this is totally unnecessary, even inappropriate, for the immediate
problem.  But it might answer the tacit question about how to do similar
logic using SUMPRODUCT.

----- original message ------

"Joe User" <joeu2004> wrote in message









- Show quoted text -

Thank you both for your advice, much appreciated.

And thank you Joe for your logic around SUMPRODUCT.

 

[Jayanta Boral]

Dear Nick,

You can try in this way -

Mark the Cells as per following

A1=Start Date, B1 = End Date, C1~N1= Jan~Dec, C2....N2 = 1.....12

Put the Start Date and end date values in (A3, B3); (A4,B4) and like wise
downwards,

Your answer values will be obtained from the cells C3~N3, C4~N4, and
likewise downwards

In the cell C3 please copy the formula : - =
IF(AND($A3=0,$B3=0),0,IF(AND(C$2>=MONTH($A3),C$2<=MONTH($B3)),1,0))

Copy the cell and paste formula in other cells in the columns C to N
starting row 3
downwards.

You will have answers.

Regards,