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?
Bookmarks