+ Reply to Thread
Results 1 to 8 of 8

schedule creator with rotating overtime and shift limits

  1. #1
    Registered User
    Join Date
    03-16-2019
    Location
    newark, nj
    MS-Off Ver
    2016
    Posts
    13

    Post 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
    Attached Files Attached Files
    Last edited by nolomcdc; 03-17-2019 at 09:16 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    03-16-2019
    Location
    newark, nj
    MS-Off Ver
    2016
    Posts
    13

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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. #5
    Registered User
    Join Date
    03-16-2019
    Location
    newark, nj
    MS-Off Ver
    2016
    Posts
    13

    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. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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. #7
    Registered User
    Join Date
    03-16-2019
    Location
    newark, nj
    MS-Off Ver
    2016
    Posts
    13

    Re: schedule creator with rotating overtime and shift limits

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

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Rotating Overtime Schedule HELP!
    By MBowers2 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-28-2021, 01:18 AM
  2. Rotating Shift Schedule-Auto Populate
    By Gigem2000 in forum Excel General
    Replies: 1
    Last Post: 10-29-2018, 10:07 PM
  3. Rotating Shift schedule for 1 month
    By varun sarolkar in forum Excel General
    Replies: 0
    Last Post: 12-02-2013, 03:34 AM
  4. EOWEO 12 hour rotating shift schedule
    By moipaman in forum Excel General
    Replies: 0
    Last Post: 07-25-2013, 11:20 PM
  5. Replies: 16
    Last Post: 12-06-2012, 12:18 PM
  6. Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule
    By chips1256 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2012, 09:32 AM
  7. [SOLVED] Macro for a Rotating Shift Schedule
    By smck in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-25-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1