Count times between 2 times and 2 dates

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

2. Re: 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"))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jeremy Ellison" <JeremyEllison@discussions.microsoft.com> wrote in message
news:A9B68494-79D3-4A16-800B-379FC4E3C808@microsoft.com...
> 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...

3. Re: 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
--

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1