Date Count !

[muddan madhu]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[OssieMac]

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.)

 

[Paul J.]

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)*(D14="Completed"),C1:C4))

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

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[muddan madhu]

Its a monthly activity so any started in Jan must and should complete
it on same the month....

 

[Rick Rothstein \(MVP - VB\)]

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


Its a monthly activity so any started in Jan must and should complete
it on same the month....