# schedule creator with rotating overtime and shift limits

1. ## schedule creator with rotating overtime and shift limits

Hello all. I am new to the whole forum world and would like to see if I could get some help with an excel sheet I am trying to make. It has two parts
part one
I need to make a 14 day schedule with three different shifts 7a-3p, 3p-11p, 11p-7a. in this sheet it should also have to total amount of each shift that is filled and what needs to be filled. For example on Sundays we need 4 staff in the morning and 4 staff in the evening. I am able to make a formula that calculates it and tells me per shift. However, when I add two shifts to a person the formula will only take one shift and not both. For example is YW 4 is schedule to work Sunday 7-3 it shoes up fine, but if they work a double 7a-3p and 3p-11p, the count in the shifts filled and OT needed are off.

part two
the OT sheet. I have tab with the OT sheet. Currently I have it where a third sheet titles employees has all the employees and which ever number is placed in the employee number cell is the name at the top of the list in the OT Sheet tab and it rotates. I just want to know if there is a simpler way of doing this with like a drop box of something. Sorry if all these questions have a simple answer. I am new to this and trying to teach myself excel in the process

2. ## Re: schedule creator with rotating overtime and shift limits

Hello nolomcdc and Welcome to Excel Forum.
The way the hours are entered will make a difference. However lets say that cell C15 contains 7a-3p;3p-11p. We could use wildcard characters in the COUNTIF function as in: =COUNTIF(C4:C19,"*"&\$A32&"*")
Note that 'on' has been removed from cells A32:A34.
I will have to look at part two later if no one else has solved by then.
Let us know if you have any questions.

3. ## Re: schedule creator with rotating overtime and shift limits

Thank you for the reply, however, the attachment you added did not open, and I am not quite sure I understand fully. I changed the A32:A34 and removed the "On" part to make sure the times match. I changed the formula in C32 from -COUNTIF(C4:C19, "7a-3p") to =COUNTIF(C4:C19,"7a-3p"&\$A32&"3p-11p") and its still not counting correctly in C32 and C33 when i enter 7a-3pm and 3p-11p it still only adds it to C33 and removes it from C32. Did I enter the wrong formula

4. ## Re: schedule creator with rotating overtime and shift limits

Really don't know why the file attached to post #2 would not have opened, just a standard .xlsx file and it just opened for me without a problem. Please check again and if there is still an issue I'll re upload the file.
The formula for C32:C34 should be =COUNTIF(C4:C19,"*"&\$A32&"*") not =COUNTIF(C4:C19,"7a-3p"&\$A32&"3p-11p")
The asterisks are wildcard characters meaning that anything may come before or after the text in the referenced cell (A32).
Also be sure that there are no trailing spaces in cells A32:A34 meaning when you select cell A32 and press the F2 key the cursor should be immediately following the p in 3p
I used a colon to separate the shifts, however checking again either an ampersand or the word 'and' should work and it should even work if you put in 7a-3pm instead of 7a-3p as it was originally designed to count.
Let us know if you have any questions.

5. ## Re: schedule creator with rotating overtime and shift limits

THANK YOU SO MUCH! I tested it with a mix of all three schedule. This is amazing thank you!

6. ## Re: schedule creator with rotating overtime and shift limits

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

7. ## Re: schedule creator with rotating overtime and shift limits

Will do. Do I create a new thread for the second part?

8. ## Re: schedule creator with rotating overtime and shift limits

I had forgotten that there was a second part. Still I would say yes, mark this thread as 'Solved', then open a new thread that is dedicated to populating the OT sheet. I would suggest explaining how you want this done in more detail, including perhaps manually placing numbers/names where you want them to go. In that way contributors will have a better idea of the objective and will have values by which to test their formulas/code.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)