+ Reply to Thread
Results 1 to 2 of 2

conditional formatting formula for detecting week number in a month

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    1

    conditional formatting formula for detecting week number in a month

    I have my sheet set up as A: = Date; B: = Day; C: = cell to be formatted;

    The date column will vary from (1st - 28th) -> (1st - 31st) depending on the month number.
    The day column will change the first day of the month depending on the month number and fill the sequence.

    As the weeks are split amongst the dates of the month, the number of weeks in a given month can change from 4 - 5.

    I have color-coded these week numbers with fill colors:

    pink for week 1;
    yellow for week 2;
    green for week 3;
    gray for week 4;
    tan for week 5;
    Orange for weekends;

    this means that the colors take up to 5 cells consecutively (The exceptions are week1 and week5 which can take 1 - 5 cells depending on when they fall in the month)
    the 5 cells can fall within a range of 10 consecutive cells depending on when they fall in the month.

    I am trying to write a formula so that a cell changes to yellow if it is in the 2nd grouping of weekday adjacent cells and is not adjacent to a weekend day.

    The formulae I am using for the cell in question are:

    - yellow (week2)=IF(AND(OR(B4="Sun",B5="Sun",B6="Sun",B7="Sun",B8="Sun",B9="Sun"),OR(B10<>"Sat",B10<>"Sun")),TRUE,FALSE)
    - orange (weekend)=IF(OR(B10="Sat",B10="Sun"),TRUE,FALSE)

    The cell directly above this is behaving as desired using the following formulae:

    - yellow (week2)=IF(AND(OR(B4="Sun",B5="Sun",B6="Sun",B7="Sun",B8="Sun"),OR(B9<>"Sat",B9<>"Sun")),TRUE,FALSE)
    - orange (weekend)=IF(OR(B10="Sat",B10="Sun"),TRUE,FALSE)

    The 2 sets of formulae should be more or less identical (adjusted for their position on the sheet but function similarly)
    Would anyone be able to advise as to why the first set of formulae are not yielding the expected results?

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,571

    Re: conditional formatting formula for detecting week number in a month

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

+ 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. Week number in month determined by 3/4 weekdays in week within said month
    By atearth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2017, 02:36 PM
  2. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  3. Formula to give week number of a month according to date of the month
    By tukae in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2016, 11:14 PM
  4. [SOLVED] Highlight week number using Conditional formatting - shows incorrect week
    By spliffter in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-26-2016, 09:52 AM
  5. Replies: 5
    Last Post: 10-03-2014, 11:40 AM
  6. Formula for Week number within a fiscal month
    By GerryT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2012, 08:14 PM
  7. Replies: 1
    Last Post: 03-10-2006, 05:15 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