Count based on date/time conditions

Hello,

I would really appreciate any help with this. I'm trying to create an excel formula to keep track of Overtime based on date/time range. The Overtime must be counted per 30 mins intervals and 2 separate worksheets. On the first worksheet (Named OT Tracker) the columns ID, Name, Supervisor, Day, Date, OT Starts, OT Ends and Total Hours must be filled depending on the dates of the Overtime per Employee. On the second workseet (named "Coverage by Intervals") we have a table with the dates the Overtime is required as columns and the raws are the hours per 30 minutes intervals on which the OT is required (Example: 15:30 - 16:00, 16:00 - 16:30). What I'm looking for is the following: let's say employee 1 signs up for Overtime on 11/23/2012 from 16:00 to 17:30, once the information is added on the OT Tracker worksheet is added the table on "Coverage by Intervals" should count 1 on the cells for 11/23/2012 for the intervals 16:00 - 16:30, 16:30 - 17:00 and 17:00 - 17:30. If a second employee is added and he wants OT for a number of hours within that range the formula should count 2 on the specified cells.

I already tried with the "countifs" but I keep getting an error. I would really appreciate at least help with how the formula for 1 cell should go so I can keep on working with the others.

Any help is really appreciated

(sorry for my english, not my native)

Can you post your excel file. Also it would be great if you write your desired outcome there

Sure,I'm posting the file.

now, on the file you'll see 4 worksheets. On the OT Tracker tab we insert the employee information, date and time range for the overtime

On the Coverage by Intervals we keep track of how many employees we need based on the staffing needs per 30 minutes intervals.

What I'm looking for is the formula to count how many employees sign up for Overtime based on the intervals. Let's say 1 employee would like to make OT from 15:00 to 16:00 on 11/20/2012. The formula should help us count 1 on the fields 15:00 - 15:30 and 15:30 - 16:00 for the date 11/20/2012. If another employee would like OT from 15:00 to 17:00 than it should count "2" on the mentioned 2 fields and 1 on the fields 16:00 - 16:30 and 16:30 - 17:00 for the desired date.


This is what I have so far:

=COUNTIFS('OT Tracker'!$E:$E,'OT Tracker'!$E:$E="11/16/2012",'OT Tracker'!$F:$F,"'OT Tracker'!$F:$F>15:00",'OT Tracker'!$G:$G,'OT Tracker'!$G:$G<15:30)

E is the date
F is when the segment starts
G is when the OT segment ends

However, this only gives me 0's

to be more explicit, this is what I want the formula to do:

this is the chart:

Time 11/15/2012 11/16/2012 11/17/2012 11/18/2012 11/19/2012 11/20/2012 11/21/2012 11/22/2012 11/23/2012 11/24/2012 11/25/2012
DAY Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Agents Requested per Interval 12 12 12 12 12 12 12 12 12 12 12
15:00 - 15:30
15:30 - 16:00
16:00 - 16:30
16:30 - 17:00
17:00 - 17:30
17:30 - 18:00
18:00 - 18:30
18:30 - 19:00
19:00 - 19:30
19:30 - 20:00
20:00 - 20:30
20:30 - 21:00


and this is the OT Tracker Tab:


SALID Name Team Manager Day Date OT Starts Ot Ends Total Hours


If I add 1 like this:

SALID Name Team Manager Day Date OT Starts Ot Ends Total Hours
7986 Mitchel Quijano Claudia Coto Monday 11/19/2012 16:00 21:00 5:00


The chart should count 1 on the 10 intervals contained within that range on the 11/19/2012, looking like this:

Time 11/15/2012 11/16/2012 11/17/2012 11/18/2012 11/19/2012 11/20/2012 11/21/2012 11/22/2012 11/23/2012 11/24/2012 11/25/2012
DAY Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday
15:00 - 15:30
15:30 - 16:00
16:00 - 16:30 1
16:30 - 17:00 1
17:00 - 17:30 1
17:30 - 18:00 1
18:00 - 18:30 1
18:30 - 19:00 1
19:00 - 19:30 1
19:30 - 20:00 1
20:00 - 20:30 1
20:30 - 21:00 1

Hi. I upload the file. In "Result" tab it counts registered over times by 30 minutes. I did a little changes on your table. If anything wrong correct me. Formula is:
=SUM(IF(('OT Tracker'!$F$2:$F$89<=Result!$A4)*('OT Tracker'!$G$2:$G$89>=Result!$B4)*('OT Tracker'!$E$2:$E$89=Result!C$1)=1,1,0)) Ctrl+Shift+Enter

Excellent! thank you very much. I never thought about that posibility.

Just one more question: how would it go if I want it to take into consideration any incoming entry? I see the formula only takes into consideration the 86 raws contained on the OT Tracker tab. I tried to change the formula to sum all raw F and G but it just counts 0 on all of them.

Originally Posted by lrmorales87

Excellent! thank you very much. I never thought about that posibility.

Just one more question: how would it go if I want it to take into consideration any incoming entry? I see the formula only takes into consideration the 86 raws contained on the OT Tracker tab. I tried to change the formula to sum all raw F and G but it just counts 0 on all of them.

Chnage the number 86 to the whatever number you want. But do not select entire row. Also, pay attention that, ranges should be equal (ex F2:F1000) in all part of formula