Counting average number of days between 2 dates

R

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
 
P

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
 
R

RobertK

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


Pete_UK said:
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
 

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