+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting for weekends

  1. #1
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Conditional formatting for weekends

    Hello everyone,

    I have a shift schedule workbook that needs repair

    I want CF, If :

    - employee has already worked 3 weekends (saturday or sunday, or both), and user wants to assign job for a day on the last weekend.

    In other words, employee must be free at least one weekend in month, otherwise CF must colour weekend cells.

    Please take a look at sample, CF should look something like that - I provided only one employee, but CF should work for all !

    P.S. : A VBA solution would be even better (with msgbox for warning) - but don't know that either...

    If anyone has a suggestion, please provide me an answer
    Attached Files Attached Files
    Last edited by Lukael; 10-10-2014 at 08:11 AM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Conditional formatting for weekends

    Try the formula

    =SUM(IF(SUM($L$21:$O$21)>0,1,0),IF(SUM($Z$21:$AB$21)>0,1,0),IF(SUM($AN$21:$AP$21)>0,1,0),IF(SUM($BB$21:$BD$21)>0,1,0)) >3

    and applies to

    =$L$17:$N$17,$Z$17:$AC$17,$AN$17:$AQ$17,$BB$17:$BE$17
    Martin

  3. #3
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Conditional formatting for weekends

    Hi mrice,

    Tried that and It works, but I applied CF on whole row.

    I have worksheets for all months, so cells for weekends changes in different months. Any way to eliminate this, so only weekends would be coloured, no matter where they are in row ?

    Thanks for help !!

  4. #4
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Conditional formatting for weekends

    Quite frustrating, nobody answers

    Still figuring out how I could do this, but no solution so far, so please some suggestions, don't be shy

  5. #5
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Conditional formatting for weekends

    This is closest solution :

    =SUMPRODUCT(--(WEEKDAY(F17:BN17;2)>5);F21:BN21)

    It calculates hours worked only on weekends. But I need to CF, If at least ONE whole weekend (Saturday + Sunday) isn't without assigned jobs..

    I tried with all COUNT functions, but they all return #VALUE errors.

    Any other options ?
    Last edited by Lukael; 10-10-2014 at 08:14 AM.

+ 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. conditional formatting for weekends
    By Lukael in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-24-2014, 05:35 PM
  2. Conditional Formatting Around Weekends
    By robatbrightstar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2013, 02:43 PM
  3. Conditional Formatting weekends
    By IKZOUHETNIETWETEN in forum Excel General
    Replies: 7
    Last Post: 02-22-2013, 01:58 AM
  4. [SOLVED] Conditional Formatting - shading weekends
    By jennbates in forum Excel General
    Replies: 2
    Last Post: 05-03-2012, 02:37 PM
  5. Conditional formatting for weekends
    By monozoli in forum Excel General
    Replies: 2
    Last Post: 04-13-2008, 01:44 PM

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