Using an Array to calculate the number of days between dates and..
 

Hi,

I have a list of Start Dates starting in A2 and running down to A35. I also
have a list of End Date strting in B2 and running down to B35 (Note: Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates and..
 

Hi

On the same sheet, in cell F1 enter 5, G1 10, H1 20 and in I1 100 (or a
large number of days that will not be exceeded)
In E2 enter Accepted, and in E3 enter Rejected
en cell F2 enter
=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

and copy across through cells G2:I2
Copy F2:I2 down to F3

--
Regards

Roger Govier


"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates and..
 

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates a
 

Roger / Bob,

Thankyou for your assistance, I am a long way closer to were I want to be.

Rogier,

I have 1 question about you solution. Everything appears to work perfectly
apart from 1 bit. Were the Start and End dates are the same, the value is 0.
The formula doesnt appear to account for this and I dont seem to be able to
adapt it to do so. So it break the numbers down from 1 - 5 days, 6 - 10 days
and so on ok, but it wont allow for the open and close to be the same day.

Rgds Paul

"Roger Govier" wrote:

Hi

On the same sheet, in cell F1 enter 5, G1 10, H1 20 and in I1 100 (or a
large number of days that will not be exceeded)
In E2 enter Accepted, and in E3 enter Rejected
en cell F2 enter
=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

and copy across through cells G2:I2
Copy F2:I2 down to F3

--
Regards

Roger Govier


"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates a
 

I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to A35. I
also
have a list of End Date strting in B2 and running down to B35 (Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates a
 

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and
end date
and list it as the number approved, rejected in 5 days or less : 6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates a
 

Roger,

I figured it out but am still confused to how it worked.

Is there any way I can ask a favour, is it possible you could take me
through what is happening in the calculation?

I understand that it is taking the product of the array and multiplying it
by the product of the second array but i just dont see how it arrives at 1 or
2 or whatever :-)

Rgds Paul

PS i will understand if this is to long and arduos.


"Roger Govier" wrote:

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and
end date
and list it as the number approved, rejected in 5 days or less : 6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates a
 

Hi Paul

=SUMPRODUCT(($B$2:$B$6<"")*
($B$2:$B$6-$A$2:$A$6E$1)*
($B$2:$B$6-$A$2:$A$6<=F$1)*
($C$2:$C$6=$E2))

The first term inside the Sumproduct, is testing whether the item is
Closed - this returns True or False
The next tests whether the Interval between the two dates is Greater
than the First date band, which I had left blank to start at 0, and you
amended to -1 to pick up 0 - again True or False
The Next term tests whether the interval is Less than or Equal to the
Second Date band (5 days) - again True or False
Finally, the last check is whether column C contains "Approved" (cell
E2) - again True or False

Because these Terms are being multiplied together, this coerces the
Trues' to 1's and False's to 0's

so 1 x 1 x 1 x 1 =1
but 0 x 1 x 1 x 1 = 0
and a 0 for any of the 4 will return 0, so only anything which satisfies
all conditions will return a 1.

Sumproduct then adds these together to give the totals for each group.

Bob has a great treatise on Sumproduct on his site at
http://xldynamic.com/source/xld.SUMPRODUCT
--
Regards

Roger Govier


"phocused" wrote in message
...
Roger,

I figured it out but am still confused to how it worked.

Is there any way I can ask a favour, is it possible you could take me
through what is happening in the calculation?

I understand that it is taking the product of the array and
multiplying it
by the product of the second array but i just dont see how it arrives
at 1 or
2 or whatever :-)

Rgds Paul

PS i will understand if this is to long and arduos.


"Roger Govier" wrote:

Hi Paul

Well figured out!!
Many thanks for letting us know you resolved the problem.

--
Regards

Roger Govier


"phocused" wrote in message
...
I figured it out, woohooo!

If I put -1 into cell e1 it performs the calculation on the dates
that
are
equal as well.

thanks

Rgds Paul

"Bob Phillips" wrote:

=SUMPRODUCT((B1:B10-A1:A10),--(C1:C10="Approved"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"phocused" wrote in message
...
Hi,

I have a list of Start Dates starting in A2 and running down to
A35. I
also
have a list of End Date strting in B2 and running down to B35
(Note: Not
all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start
and
end date
and list it as the number approved, rejected in 5 days or less :
6
to 10
days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days
10 - 20
days 20@
Approved 3 2
10 0
Rejected 1
0
0 0

I can do this over several calculations using several
worksheets,
but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates and..
 

Ah, the cloud is lifted. Roger, now I get it. I was taking things at face
value. The product of the Serial Numbers of the dates times the array..... No
wonder I was getting no where.

True and False.

Excellent, thanks you for your patience.

Rgds Paul

"phocused" wrote:

Hi,

I have a list of Start Dates starting in A2 and running down to A35. I also
have a list of End Date strting in B2 and running down to B35 (Note: Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end date
and list it as the number approved, rejected in 5 days or less : 6 to 10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 - 20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul

Using an Array to calculate the number of days between dates and..
 

Hi Paul

You're more than welcome. Enjoy the wonders of Sumproduct!!!

--
Regards

Roger Govier


"phocused" wrote in message
...
Ah, the cloud is lifted. Roger, now I get it. I was taking things at
face
value. The product of the Serial Numbers of the dates times the
array..... No
wonder I was getting no where.

True and False.

Excellent, thanks you for your patience.

Rgds Paul

"phocused" wrote:

Hi,

I have a list of Start Dates starting in A2 and running down to A35.
I also
have a list of End Date strting in B2 and running down to B35 (Note:
Not all
start dates will have a corresponding end date)
Listed in C2 is a status, Approved, Rejected etc

Problem
I am trying to calculate the number of days between the start and end
date
and list it as the number approved, rejected in 5 days or less : 6 to
10 days
: 10 to 20 days or greater than 20Days
I also want to break this down by month

So i would end up with a result looking like

Stats for December
number of calls closed in <= 5 days 6 - 10 days 10 -
20
days 20@
Approved 3 2
10 0
Rejected 1 0
0 0

I can do this over several calculations using several worksheets, but
i
would like to do it in a single sheet using an array

Is this possible ?

Rgds Paul