Hi All,

In a spread sheet i have dates in col A and Col B (start date and end
date).
Col A Col B Dates Count Status
01-jan-08 10-jan-08 10 days Completed
10-feb-08 15-feb-08 5 days Completed
07-jan-08 09-jan-08 2 days Pending
10-jan-08 12-jan-08 2 days
Completed

If 01-jan-08 to 10-jan-08 is considered 10 days (the difference in the day
number plus 1), then why are the other date differences not 6, 3 and 3 days
respectively (they are only the difference in the day numbers... no plus 1
is applied)? How do you want the "date count" calculated?

Rick


"muddan madhu" wrote in message
...
Hi All,

In a spread sheet i have dates in col A and Col B (start date and end
date).
Col A Col B Dates Count Status
01-jan-08 10-jan-08 10 days Completed
10-feb-08 15-feb-08 5 days Completed
07-jan-08 09-jan-08 2 days Pending
10-jan-08 12-jan-08 2 days
Completed
.
.

Like this i have more than 200 rows. I need Avg number of "days" for
the "completed" rows based on the month.
Eg., For the month of JAN - Avg number of days = 10+2 = 12/2 = 6 days.

Dates count I have inserted for example, actually in original file
there is no column called Dates count.

Is this possible to get desired answer from excel formulas itself ?

Thanks in advance.

I suggest that you look up Autofilter in Help and use it in conjunction with
SUBTOTAL function.

If you leave 5 or 6 blank rows above your column headers and then place the
cursor in the far left cell (Col A) below the headers and freeze the panes ,
you can place your subtotal functions in the top 5 or 6 rows and you will not
loose them off the screen.

The Autofilter allows you to apply the filters required on each column and
the SUBTOTAL function will provide the counts, sum, average as required.
(Also look up SUBTOTAL in help because it is a very useful function in
conjunction with Autofilter.)

--
Regards,

OssieMac


"muddan madhu" wrote:

Hi All,

In a spread sheet i have dates in col A and Col B (start date and end
date).
Col A Col B Dates Count Status
01-jan-08 10-jan-08 10 days Completed
10-feb-08 15-feb-08 5 days Completed
07-jan-08 09-jan-08 2 days Pending
10-jan-08 12-jan-08 2 days
Completed
.
.

Like this i have more than 200 rows. I need Avg number of "days" for
the "completed" rows based on the month.
Eg., For the month of JAN - Avg number of days = 10+2 = 12/2 = 6 days.

Dates count I have inserted for example, actually in original file
there is no column called Dates count.

Is this possible to get desired answer from excel formulas itself ?

Thanks in advance.

If you DID include a column counting the date difference (and you decided on
the proper math, as Rick pointed out), you could use the array formula like:

=AVERAGE(IF((MONTH(B1:B4)=1)*(D1:D4="Completed"),C 1:C4))

Press CTRL+SHIFT+ENTER after typing/modifying that formula (you will see
braces, {}, automatically appear around the formula when done properly).
----------------------------------------

"Rick Rothstein (MVP - VB)" wrote:

If 01-jan-08 to 10-jan-08 is considered 10 days (the difference in the day
number plus 1), then why are the other date differences not 6, 3 and 3 days
respectively (they are only the difference in the day numbers... no plus 1
is applied)? How do you want the "date count" calculated?

Rick


"muddan madhu" wrote in message
...
Hi All,

In a spread sheet i have dates in col A and Col B (start date and end
date).
Col A Col B Dates Count Status
01-jan-08 10-jan-08 10 days Completed
10-feb-08 15-feb-08 5 days Completed
07-jan-08 09-jan-08 2 days Pending
10-jan-08 12-jan-08 2 days
Completed
.
.

Like this i have more than 200 rows. I need Avg number of "days" for
the "completed" rows based on the month.
Eg., For the month of JAN - Avg number of days = 10+2 = 12/2 = 6 days.

Dates count I have inserted for example, actually in original file
there is no column called Dates count.

Is this possible to get desired answer from excel formulas itself ?

Thanks in advance.

Eg., For the month of JAN - Avg number of days
= 10+2 = 12/2 = 6 days.

Think you missed one for Jan.

Are these possible:

What if a date range spans more than a single month?

1/29/2008 - 2/5/2008

What if a date range spans multiple months:

1/29/2008 - 6/21/2008

--
Biff
Microsoft Excel MVP


"muddan madhu" wrote in message
...
Hi All,

In a spread sheet i have dates in col A and Col B (start date and end
date).
Col A Col B Dates Count Status
01-jan-08 10-jan-08 10 days Completed
10-feb-08 15-feb-08 5 days Completed
07-jan-08 09-jan-08 2 days Pending
10-jan-08 12-jan-08 2 days
Completed
.
.

Like this i have more than 200 rows. I need Avg number of "days" for
the "completed" rows based on the month.
Eg., For the month of JAN - Avg number of days = 10+2 = 12/2 = 6 days.

Dates count I have inserted for example, actually in original file
there is no column called Dates count.

Is this possible to get desired answer from excel formulas itself ?

Thanks in advance.

Are these possible:

What if a date range spans more than a single month?

1/29/2008 - 2/5/2008

Or even.... 12/15/2007 - 1/15/2008

Rick

Depending on what your answer is to my other question about the difference
in your subtractions, I think one of these is what you want...

If 01-jan-08 to 10-jan-08 is 10 days
*******************************************
=SUMPRODUCT((A1:A10<"")*(B1:B10-A1:A10+1)*(MONTH(A1:A10)=1)*(C1:C10="Completed"))

If 07-jan-08 to 09-jan-08 is 2 days
*******************************************
=SUMPRODUCT((A1:A10<"")*(B1:B10-A1:A10)*(MONTH(A1:A10)=1)*(C1:C10="Completed"))

For either of the two formulas, change all of the A10s and B10s to a row
number reference that is greater than any row that you expect to have data
in.

Rick


"muddan madhu" wrote in message
...
Its a monthly activity so any started in Jan must and should complete
it on same the month....

On Aug 21, 11:18 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Are these possible:

What if a date range spans more than a single month?

1/29/2008 - 2/5/2008

Or even.... 12/15/2007 - 1/15/2008

Rick