Scheduling Problem

larsenee

New Member
Joined
Nov 12, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Nov 12, 2005
Messages
2
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,381
Members
412,589
Latest member
ArtBOM
Top