I need to figure out how to write a formula to calculate how many agents are working during any given time segment. This is an example of what the sheets I'm working off of look like, but at work each chart snippet is full size and encompasses a week, with the time slots covering 12 AM- 11:30 PM, and is on a separate spreadsheet in the same workbook. I'm trying to write a formula to do this, since counting by hand is prone to error.
agent calculation.xlsx
Sorry, new here and didnt know how to do this. here's an example that doesn't involve the attachment.
Agent Name Sunday Sunday Monday Monday John Smith 7:00 4:00 OFF OFF Jane Doe 7:00 4:00 7:00 4:00
Time Slot Sunday Monday 6:30 AM 7:00 AM 7:30
Last edited by Sev129; 08-11-2014 at 07:58 PM.
Based on your sample, try this, copied down...
=COUNTIF($B$2:$D$3,A8)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
but doesn't that only count it if it matches that particular time? I'm trying to make a formula I could drag to all cells so it would automatically total the number of workers on shift at that time and cease to count them when their shift ended.
Update: yeah, that formula ceases to count after 7:00. So even though they would be on shift at 7:30, it registers at 0.
I tried a COUNTIF function before that hinged upon two IF functions as the criteria, but it wasn't working properly.
it was something like Countif($A$2:$A$3, AND(if(A8>=B2), if(A8<=C2)))
Last edited by Sev129; 08-11-2014 at 10:49 PM.
OK perhaps you could provide a sample with more data, and show what your expected outcome would be? (I thought you wanted to count how many were "allocated" to that time)
the goal is to show how many are on shift at that particular time. so for this sample, the correct answers would be 0 at 6:30 on sunday, 2 at 7am, 2 at 7:30,
and on monday, 0 at 6:30, 1 at 7:00 and 7:30.
Hi Sev129,
I have been working on a similar problem,
Looking at your spreadhseet attachment i have created teh following calculation;
=(COUNTIF($C$2:$C$3,">="&$A8)-COUNTIF($B$2:$B$3,">"&$A8))
You should be able to drag the formula down, however you will need to change the range from sunday to monday, then tuesday etc as you drag across, again once dragged across, should be able to drag down. This will tell you how many people are working at a specific time.
the only downside to this formula is that i havent yet worked out how to calculate for a shift where it finishes the next day ... i'll probably open a new thread for that little beauty!
hope this helps...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks