Solved Conditional formatting with dates and weekend special rules

January 2, 2019 at 12:17:09
Specs: Windows 7
I need to highlight cells based on the date in column AG. I am using conditional formatting but can't seem to get the formula right.

Logic:
If Today() is a Monday I need to highlight the cell if the date in AG is the PREVIOUS Friday, Saturday and Sunday
If Today() is a Tuesday through Friday then I need to highlight if the date in AG is the previous day

I started the formula like this but it is not working properly:
=OR(TODAY()=WEEKDAY(3),WEEKDAY(4),WEEKDAY(5),WEEKDAY(6),AG10=TODAY()-1,OR(IF(TODAY()=WEEKDAY(2),AG10<TODAY()-4)))


See More: Conditional formatting with dates and weekend special rules

Reply ↓  Report •

✔ Best Answer
January 9, 2019 at 06:48:22
Just to put a bow on this on, I got a countifs formula from the stackoverflow site that works well:

=IF(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6),COUNTIF(AG:AG,TODAY()-1),IF(WEEKDAY(TODAY())=2,
COUNTIFS(AG:AG,">=" & TODAY() -3,AG:AG,"<="&TODAY()-1),0))

message edited by mecerrato



#1
January 2, 2019 at 19:32:36
Not completely sure but these two formulas seem to work:

=AND(WEEKDAY(TODAY())=2,AG1>=TODAY()-3)

=AND(WEEKDAY(TODAY())>2,WEEKDAY(TODAY())<6,AG1=TODAY()-1)

Yo should be able to combine them with this:

=OR(AND(WEEKDAY(TODAY())=2,AG1>=TODAY()-3),AND(WEEKDAY(TODAY())>2,WEEKDAY(TODAY())<6,AG1=TODAY()-1))

See how they work for you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
January 3, 2019 at 17:05:58
The combined formula seems to work but I won't be able to fully test until Monday to test for the weekend functionality.

I also received this formula from stackoverflow forum:
=OR(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6,INT(AG10)=INT(TODAY()-1)),AND(WEEKDAY(TODAY())=2,INT(AG10)>=INT(TODAY()-3),INT(AG10)<=INT(TODAY()-1)))

I would like to incorporate your formula with a countifs formula so I can count the amount of loans that meet the criteria, I tried this but am getting 0 as a result when I should be getting 2:
=COUNTIF($AG$10:$AG$500,(OR(AND(WEEKDAY(TODAY())=2,AG10:AG500>=TODAY()-3),AND(WEEKDAY(TODAY())>2,WEEKDAY(TODAY())<6,AG10:AG500=TODAY()-1))))

message edited by mecerrato


Reply ↓  Report •

#3
January 3, 2019 at 17:39:21
You would probably be better off using a VBA solution to count the colors.

You would need some type of an Array formula, but with your Conditional Formatting
conditions being so convoluted, it would be difficult to replicate in an array.
Perhaps a SUMPRODUCT() might work.

Is there some way to simplify your data setup, perhaps add helper columns for day of week or some such, so your requirements are more straightforward?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
January 9, 2019 at 06:48:22
✔ Best Answer
Just to put a bow on this on, I got a countifs formula from the stackoverflow site that works well:

=IF(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6),COUNTIF(AG:AG,TODAY()-1),IF(WEEKDAY(TODAY())=2,
COUNTIFS(AG:AG,">=" & TODAY() -3,AG:AG,"<="&TODAY()-1),0))

message edited by mecerrato


Reply ↓  Report •

Ask Question