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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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>
incorrect.bmp
</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>
correct.bmp
</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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,905
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top