+ Reply to Thread
Results 1 to 8 of 8

working 3 weekends in a row - Is there a formula that can help determine

  1. 11-04-2017, 12:27 PM #1
    WTODD
    WTODD is offline
    Registered User
    Join Date
    09-06-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10

    Red face working 3 weekends in a row - Is there a formula that can help determine

    Hi There

    I have a staff schedule and I am wanting to see if there is a formula that I can use to determine if someone has worked 3 weekends in a row(this causes overtime and I would like to avoid if possible). I am not sure if a sumif formula would work, but I would like to either select the whole range and have it determine if shifts were worked on Friday N Saturday D or N and Sunday D or N and if any of the previous are true. for the previous weekend and the next weekend on the schedule. I am not sure if I can just apply to the "weekend" cells or the whole range or if there would be another formula that would be better ie. count, and I have no idea what it would look like.

    If anyone has any suggestions, I would really appreciate it. Thanks in advance for your time and brainpower
    Reply With QuoteRegister To Reply
  2. 11-04-2017, 12:36 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,593

    Re: working 3 weekends in a row - Is there a formula that can help determine

    Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 11-04-2017, 01:02 PM #3
    WTODD
    WTODD is offline
    Registered User
    Join Date
    09-06-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: working 3 weekends in a row - Is there a formula that can help determine

    If the file attached, it should show a sample of staff and their schedule for a couple of weeks. There are three weekends included.
    I am looking for a formula that will color the first weekend where shifts are scheduled "green", then if shifts are scheduled the next
    consecutive weekend those shifts would be "orange" (like a caution to signal the next weekend if shifts were scheduled they would be
    overtime.Then if a shift is scheduled on the 3rd consecutive weekend cells would be "black". Cell color isn't that important,
    I just need something that will signal that the shifts on the 3rd weekend are present and I would be able to reschedule in
    order to avoid overtime
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-04-2017, 01:03 PM #4
    WTODD
    WTODD is offline
    Registered User
    Join Date
    09-06-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: working 3 weekends in a row - Is there a formula that can help determine

    Thank you for the instruction on how to attach a file. I have tried to use the attachment button before and just thought it was me.

    Thanks again
    Reply With QuoteRegister To Reply
  5. 11-05-2017, 02:27 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,540

    Re: working 3 weekends in a row - Is there a formula that can help determine

    This proposed solution employs a two part helper table (highlighted in blue and red) which may be moved and/or hidden for aesthetic purposes.
    The formula that populates the blue part of the helper table is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The formula that populates the red part of the helper table is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Three conditional formatting rules are then applied to the 'rota' table (range B32:R37).
    Green: =B41=1
    Orange: =B41=2
    Black (with red font): =B41=3
    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.
    Reply With QuoteRegister To Reply
  6. 11-10-2017, 08:54 AM #6
    BlindAlley
    BlindAlley is offline
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: working 3 weekends in a row - Is there a formula that can help determine

    Did you get this fixed ?
    Reply With QuoteRegister To Reply
  7. 11-11-2017, 01:35 PM #7
    WTODD
    WTODD is offline
    Registered User
    Join Date
    09-06-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: working 3 weekends in a row - Is there a formula that can help determine

    Looks great for the sample size that I posted JeteMC. Thanks so much.

    I am wondering though if it can be altered as the schedule actually contains 12 weeks. If a staff does not work three weekends in a row, then the count starts over. It is confusing in that weekend 1,2,3 counts as 3 weekends in a row but as well weekend 2,3,4 counts etc...

    Thanks again for your help with this
    Reply With QuoteRegister To Reply
  8. 11-11-2017, 07:55 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,540

    Re: working 3 weekends in a row - Is there a formula that can help determine

    See if the following modifications work.
    1) B40 and to the right: =WEEKDAY(B$31,2)
    2) Conditional formatting rule for orange: =B41>=2
    3) Conditional formatting rule for black: =AND(B41>=3,MOD(B41,3)=0)
    4) Order of conditional formatting rules: Black, Orange, Green
    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Determine a work week date question if days off are not on weekends
    By lilsnoop in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 08-05-2016, 09:29 PM
  2. Have to calculate working hours excluding Weekends,Holidays and NON Working Hours
    By Bob-1383 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2013, 10:45 AM
  3. [SOLVED] Formula to Calculate Working Days and Weekends for Individual Staff
    By jlo33 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-12-2013, 12:37 PM
  4. [SOLVED] Determine End Date that omits weekends and holidays
    By mrh_consulting in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 11:44 PM
  5. Excel 2007 : Need help in excel formula for adding working hours to a date and time excluding weekends
    By vinothkumar-s in forum Excel General
    Replies: 0
    Last Post: 04-16-2012, 05:47 AM
  6. Excel 2007 : Need help in excel formula for adding working hours to a date and time excluding weekends
    By vinothkumar-s in forum Excel General
    Replies: 0
    Last Post: 04-16-2012, 04:31 AM
  7. [SOLVED] In MS Excel 03, how do I determine an end date (with weekends)?
    By Jen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2006, 08:10 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1