If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP ASAP!

**colton14** · 07-18-2016, 11:24 AM

Hello Everyone!

Attached below you will see a picture of the work schedule I have created for the new quick service restaurant I am opening that serves gourmet sub sandwiches. I have run into a road block with trying to simply the readability of the schedule by adding manpower at the bottom. The man power section is a synopsis of how many people are on during critical times. I figured that an if formula would do the trick but I can't quite figure it out.

The metrics are as follows: opening is anyone that is scheduled at 10:00 AM, lunch rush is anyone scheduled at 11:00 AM, afternoon is anyone that is scheduled at 2:00 PM, dinner rush is anyone scheduled at 5:00 PM, and closers is anyone scheduled until 10:00 PM. I want to be able to quickly look at that column for the day and know if we have enough people at certain times through out the day. Please let me know if this does not make sense.

Any help is greatly appreciated!

Thanks,

Colton

2gsiaeq.png
Attachment 471058

**Toonies** · 07-18-2016, 11:51 AM

can you post an example of you spreadsheet

click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**crushdrinker06** · 07-18-2016, 11:55 AM

Firstly an excel file that has been desensitized would be helpful so that we do not have to retype your information. since you have already shown a picture what you could do is a special copy and paste. Copy your information as normal and then do a paste as values. that being said I suspect you will need and index match in an array format with an if statement deciding which cells match the criteria you are wanting. If you upload your file I will try to show this.

**Toonies** · 07-18-2016, 04:35 PM

this may help you

http://www.mrexcel.com/forum/excel-q...each-hour.html

**colton14** · 07-18-2016, 05:35 PM

Just attached the spreadsheet. I hope this helps and thanks for all of the replies!

**colton14** · 07-18-2016, 11:37 PM

Where you guys able to view my spreadsheet?!

**FDibbins** · 07-19-2016, 12:21 AM

This is what Im starting with...
=COUNTIF(D$8:D$29,10/24)
However, would these same people also be included in the lunch spot, too?

So, based on your sample file, would lunch rush be 3 (that started at 11 AM) or 6 (to include those that started at 10)?

**Toonies** · 07-19-2016, 03:00 AM

try this

Please Login or Register  to view this content.

**colton14** · 07-19-2016, 07:45 AM

FDibbins, lunch rush would include those who started at 10am as well. It is showing man power numbers at each given point in the day to assist with making sure we do not have any under staffed time periods. I still need to incorporate the position each employee will work. Those include cook, cashier, prep, assembler, and maintenance. I was thinking about scheduling my employees to do each task weekly and breaking each position into its own category or in regards to daily, I would add a merged cell above the two shift hours that would have cook, cashier, prep, A, or M. What do you guys think? Any better suggestions?

**FDibbins** · 07-19-2016, 10:51 AM

I still need to incorporate the position each employee will work. Those include cook, cashier, prep, assembler, and maintenance.

I dont see that, presumably you have not got that far yet?

To include all staff up to a certain time...
=COUNTIFS(D$8:D$29,"<="&11/24)
Change the 11 to whatever hour you need
You later expand on that to include worker types just by adding extra ranges/criterias

**colton14** · 07-19-2016, 06:38 PM

Thanks again for all of your help. I have not got that far yet but I will be implementing that right now. I will let you know how it goes. Thanks!

**colton14** · 07-20-2016, 01:51 AM

Just got the schedule to look and function how I want it. Could you please show me or explain in more depth what you mean on how I can add job titles to the schedules now? I am trying to streamline everything about this schedule so that way I can have a manager complete it with little assistance. Once I proficiently add the job titles, I need to figure out the best way on creating week 2's schedule that rotates weekends with the first schedule as best as possible to keep everyone happy.

I am ecstatic with the assistance I have received on this forum and I thank you very much!

**Toonies** · 07-20-2016, 03:51 AM

try this, I,ve added a dropdown menu for employee tasks

**colton14** · 07-20-2016, 05:29 PM

That worked great! Please see my latest revisions and can you help me apply the same formula for the total shifts per day (SUMPRODUCT) to total positions for each given day? Thanks and it is starting to become a nice spreadsheet!

**colton14** · 07-21-2016, 09:34 AM

Bump. Almost done so can someone please help? Thanks!

**Toonies** · 07-21-2016, 09:55 AM

try this - copy over

Please Login or Register  to view this content.

**colton14** · 07-24-2016, 11:36 AM

Working on it now. Stay tuned.

**colton14** · 07-24-2016, 12:45 PM

Please see the attached excel file for my version of how to identify how many shifts of each job position are during lunch rush and dinner rush. You will notice that I have to break full time and part time up so I can make sure I have every position scheduled, which does not appear to be the best way to do this. Is there a way to have the COUNTIF formula correlate with the results from the SUMPRODUCT formula? I want to be able to easily identify how many of each position we have during lunch rush and dinner rush. No need to know all of the other time frames that were utilized for the SUMPRODUCT formula because if we have the positions covered for lunch and dinner rush, it will cover all of the other time frames due to the way the shifts are scheduled. If you think is a better way to do this please share. Thanks and I need to wrap this schedule up ASAP as we are opening in two weeks!

**colton14** · 07-25-2016, 11:24 AM

Bump. Anyone??

**JeteMc** · 07-26-2016, 03:34 PM

Try pasting the following formula in D53 and dragging down to D57:

Formula:

Please Login or Register  to view this content.

You'll then need to modify it for the Dinner Rush E53 dragged to E57 as follows:

Formula:

Please Login or Register  to view this content.

The formulas apply to Monday's Lunch and Dinner Rushes for full time employees, they will need to be modified for the other days and for part time employees.
Let me know if you have any questions.

**colton14** · 07-27-2016, 02:20 PM

How does this look?

If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP ASAP!

LinkBack

Thread Tools

Rate This Thread

Display

If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP ASAP!

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Re: If Function and Time Number Format. Creating Work Schedule w/ Manpower and NEED HELP A

Thread Information

Users Browsing this Thread

Similar Threads

Creating an employee work schedule that will calculate time worked

Need help creating a break schedule for work

Creating a Work Schedule on 24 HR format...Need Help Please

Creating work schedule in Excel

Extract text strings separated by commas and spaces

[SOLVED] Manpower Schedule

[SOLVED] Creating a work schedule

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions