Counting average number of days between 2 dates

[RobertK]

I'm trying to create a formula that will give me the Average number of Days
Activities early (-) or late(+) based upon the due date. Column B represents
the dye date and column C is the actual date delivered. In the example there
may be dates in column B and none in column C these are to ignored. I also
need the count of Activities thsat are early and those that are late. I have
thousands of rows so I don't want to add a formuls in another column that
determines each one separatly and than sum them up. I need to do it in one
step.

A B C
Act 1 2/3/09 2/5/09
Act 2 6/7/09 7/6/09
Act 3 1/5/09 3/4/09
Act 4 4/3/09 8/9/09

Robert K

 

[Pete_UK]

Try these:

=SUMPRODUCT(--(B1:B100>C1:C100),--(C1:C100<>""))

to give you a count of earlies, and this:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<>""))

to give you a count of lates (adjust the ranges to suit).

If you want the total number of days that are early you can use:

=SUMPRODUCT(--(B1:B100>C1:C100),--(C1:C100<>""),(B1:B100-C1:C100))

and the total number of late days is given by:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<>""),(C1:C100-B1:B100))

Averages can be obtained by dividing the totals by the counts.

Hope this helps.

Pete

 

[RobertK]

Thanks Pete, that did the trick.
--
Robert K

Try these:

=SUMPRODUCT(--(B1:B100>C1:C100),--(C1:C100<>""))

to give you a count of earlies, and this:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<>""))

to give you a count of lates (adjust the ranges to suit).

If you want the total number of days that are early you can use:

=SUMPRODUCT(--(B1:B100>C1:C100),--(C1:C100<>""),(B1:B100-C1:C100))

and the total number of late days is given by:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<>""),(C1:C100-B1:B100))

Averages can be obtained by dividing the totals by the counts.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Robert - thanks for feeding back.

Pete