Scheduling Problem

[larsenee]

What I am trying to do is make a schedule for work,
for our AM servers. There's one thing that's keeping
me from getting it right though. We have servers that
come in in the morning (6:00 AM and 8:00 AM), but we
also have servers that come in at 11:00 AM. We count
the number of each and denote it as: 6 + 2. 6 being
the number of AM servers, and the 2 is the number of
lunch servers. I can use the count function to count
everyone, but it automatically adds in the lunch
servers. My questions are:

1. How do I get the program to distinguish between the
two different shifts.

2. How do I get the program to denote the servers as
6+2 as opposed to 8.

larsenee

 

[Zack Barresse]

Hello there larsenee, welcome to MrExcel.com board!

Please read the Sticky threads in this forum and take a look at the HTML Maker. There is a FAQ thread in this forum and the link is at the bottom of this page. This will help you post your spreadsheet data to the board so we can get a look at what it is you are talking about. Please ensure to post a small representative sample of your data (10-20 rows) along with what the results of your questions should be and how you want it to look.

 

[larsenee]

This is the way the current schedule looks. The "# SCHEDULED" has the sum total of employees working that day.



<center>

</center>



This is how I want the schedule to look. Notice that the "# SCHEDULED" denotes between the number of people coming in before 11:00 AM (the first number) and the people who come in at or after 11:00 AM (the second number).



<center>

</center>

 

[Zack Barresse]

In D20 enter:

=SUMPRODUCT(--(D3:D18<TIME(11,0,0))*(D3:D18<>""))&IF(COUNTIF(D3:D18,">="&TIME(11,0,0))>0,"+"&COUNTIF(D3:D18,">="&TIME(11,0,0)),"")

.. copy across as desired.

Note: The cell contents in F9 will skew your results if it is not an actual time value. 11:00 AM-CL is not a valid value if that is what is entered into the cell. If you enter 11:00 AM and use the format hh:mm AM/PM"-CL" then it will show as you have shown in your postings.

HTH