Count times between 2 times and 2 dates
 

This is difficult and I'm lost! Here is the data (omitting not needed)

A D E
Control # Date Time



2 questions:

1. What formula would I use if I wanted to find out the number of #'s
ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year).
1.a IS there a way not to count diplicates of the control #
only... it would
be ok to count duplicate dates, just not duplicate of
control #'s -
which could occur unfortunately becuase of the way the
data is used.

2. What formula would I use if I wanted to find out the number of #'s
occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06?
2.a SAME as 1.a


I tried using the sumproduct formula and am completely screwing that up...
please help!

Count times between 2 times and 2 dates
 

Assume data in the table (cols A, D, E) is from row2 to row100,
col D contains real dates,
col E contains time expressed as text numbers, eg: "0000", "0100", etc

Use 2 empty cols to the right, say, cols G & H
Put in G2: =A2&"_"&D2
Put in H2: =IF(COUNTIF($G$2:G2,G2)1,"","x")
Select G2:H2, copy down to H100
(Col H will flag unique "Control # - Date" with an "x")

Then, to extract the the number of Control #'s
ocurring on Monday between 1/1/06 and 3/31/06
(counting only unique "Control # - Date")

we could put in say, I2:

=SUMPRODUCT((H2:H100="x")*(WEEKDAY(D2:D100)=1)*(D2 :D100= --"1-Jan-2006")*(D
2:D100<= --"31-Mar-2006"))

And to retrieve the number of Control #'s occurring
between 0000 hours and 0400 hours
between 1/1/06 and 3/31/06
(again, counting only unique "Control # - Date")

we could put in say, J2:

=SUMPRODUCT((H2:H100="x")*(E2:E100= "0000")*(E2:E100<=
"0400")*(D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

Adapt the ranges to suit
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jeremy Ellison" wrote in message
...
This is difficult and I'm lost! Here is the data (omitting not needed)

A D E
Control # Date Time



2 questions:

1. What formula would I use if I wanted to find out the number of #'s
ocurring on Monday between 1/1/06 and 3/31/06 (1st quarter of the year).
1.a IS there a way not to count diplicates of the control #
only... it would
be ok to count duplicate dates, just not duplicate of
control #'s -
which could occur unfortunately becuase of the way the
data is used.

2. What formula would I use if I wanted to find out the number of #'s
occurring between 0000 hours and 0400 hours between 1/1/06 and 3/31/06?
2.a SAME as 1.a


I tried using the sumproduct formula and am completely screwing that up...
please help!

Count times between 2 times and 2 dates
 

And if we don't want to count only unique "Control # - Date"'s
just remove this condition from the formula: .. (H2:H100="x")* ..
viz., use:

In I2:
=SUMPRODUCT((WEEKDAY(D2:D100)=1)*
(D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

In J2:
=SUMPRODUCT((E2:E100= "0000")*(E2:E100<="0400")*
(D2:D100= --"1-Jan-2006")*(D2:D100<= --"31-Mar-2006"))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--