+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting on Weekends

  1. #1
    Registered User
    Join Date
    11-02-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Question Conditional formatting on Weekends

    Hi all,

    I have checked through some of the other conditional formatting threads but still cannot seem to solve my issue.

    I am trying to set up a calendar style sheet with the weekends coloured.

    the sheet is set up with the below repeated across columns (eg A-C is nov, D-F is Dec, G-I is Jan ect with days in month on the rows)
    ..............A..........|.....B........| C
    =TEXT(B11,"ddd")|1/11/2017| Blank


    At this stage the closest I can get is using the formula =OR(WEEKDAY(A11,2)=6,WEEKDAY(A11,2)=7) but it only highlights the weekends in column B and all of column C


    Ideally each weekend row per month would be highlighted.

    I have attached the file for more info.

    Sorry if this doesn't make sense. I'm just as confused as you probably are ha.

    And advice welcome here


    Cheers
    Wrighty
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Conditional formatting on Weekends

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly 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 information is removed first!!

    4. Try to avoid using merged cells. 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.
    Glenn



  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,708

    Re: Conditional formatting on Weekends

    select range from A11 to right/down and use as a formatting rule formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    (yes there is A11 as refference to current cell and column(C11) used in offset to take always date from columns B, E, H (if formatting woulkd be used there etc).
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    11-02-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting on Weekends

    Quote Originally Posted by Glenn Kennedy View Post
    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly 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 information is removed first!!

    4. Try to avoid using merged cells. 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.


    Hi Glenn,

    Sorry, newbie here,

    I will try and do better next time :S
    Last edited by wrightyy; 11-02-2017 at 09:11 PM. Reason: clarity

  5. #5
    Registered User
    Join Date
    11-02-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting on Weekends

    Quote Originally Posted by Kaper View Post
    select range from A11 to right/down and use as a formatting rule formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    (yes there is A11 as refference to current cell and column(C11) used in offset to take always date from columns B, E, H (if formatting woulkd be used there etc).

    You sir are a legend and a saviour, I have no idea how or why that works but its perfect.

    Cheers!!!

  6. #6
    Registered User
    Join Date
    11-02-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting on Weekends

    Quote Originally Posted by Kaper View Post
    select range from A11 to right/down and use as a formatting rule formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    (yes there is A11 as refference to current cell and column(C11) used in offset to take always date from columns B, E, H (if formatting woulkd be used there etc).

    Hi again Kaper,


    If I want to add additional columns per month how do I need adjust the formula in the conditional formatting?


    Cheers
    Wrighty

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,708

    Re: Conditional formatting on Weekends

    Hi,
    Assuming column B holds the date for first month and each month has K columns you may try just change:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If that is not enough, follow Glenn's advice and attach representative workbook

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

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