using a time format in a count function

G

gregv7

I have a spreadsheet that has the following:
A B C
Start Time Run Day Run Time
11/14/09 12:20 AM Sat 12:20 AM
11/15/09 3:30 PM Sun 3:30PM

The cells are formatted as:
A= Date & Time
B=TEXT(A2,"ddd")
C=A2(cell reference with a time format)
My goal is to count how many times the event occurred on a particular day of
the week and count how many times it occurred during a time frame. EG Monday
between 5:00 AM & 8:00 AM
I have the count for the days of the week, I just can't get the occurrences
between the hours I need.
Thank you in advance!
 
T

T. Valko

Use cells to hold the criteria...

E2 = some day of week like Mon
F2 = lower time boundary like 5:00 AM
G2 = upper time boundary like 8:00 AM

=SUMPRODUCT(--(B2:B100=E2),--(C2:C100>=F2),--(C2:C100<=G2))
 

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

Similar Threads

separating date and time 5
Time Zone Conversion Table 1
aging calendar days 2
countif with time 3
Detailed Countdown 2
LOOKUP function?? 5
Over time? 4
Modifying a complex date formual to count months 4

Top