Countifs formula using weekday function

1
votes

I have this formula that I am using for conditional formatting that highlights cells based on the date in column AG. I got the formula from a stackoverflow genius :-)

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

The formula works great in the conditional formatting cells but I also would like to adapt it to a COUNTIFS formula so I can count the amount of records that meet the criteria.

Here is the formula: =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)))

Can someone help me tweak it to work with COUNTIF or COUNTIFS?

2
excelexcel-formula
Are the values in column AG dates or are they datetimes (i.e. include a time as well as a date)? The reason for asking is that you might be able to get rid of all the INT's which would make life simpler. – Tom Sharpe

2 Answers

2
votes

Use SUMPRODUCT:

=SUMPRODUCT(((WEEKDAY(TODAY())>= 3)*(WEEKDAY(TODAY())<=6)*(INT(AG10:AG100)=INT(TODAY()-1)))+((WEEKDAY(TODAY())=2)*(INT(AG10:AG100)>=INT(TODAY()-3))*(INT(AG10:AG100)<=INT(TODAY()-1))))

If the Dates in AG do not have time component then we can use:

=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))
1
votes

So just for completeness (with apologies to @Scott Craner for borrowing his formula) this should work even if the values are datetimes:

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

because Today()-1 is 3/1/19 00:00 and Today() is 4/1/19 00:00 so anything >= the first and < the second (e.g. 3/1/19 12:00) must be yesterday.