How to exclude Saturdays and Sundays for calculation

[James01]

Hi

I have a formula as below

=

COUNTIF(Sheet1!$AA$1:$AA$3000,TEXT(NOW()-1,"mm/dd/yy"))

How do I get this formula to skip saturday and sundays?

Thanks

 

[Fazza]

Well, the formula counts days using NOW()-1 so is checking the day before.

To exclude Saturdays and Sundays is excluding results when the day that the formula executes is Sunday or Monday.

So, maybe something like this? HTH, Fazza

=IF(OR(MOD(TODAY(),7)={1,2}),0,COUNTIF(Sheet1!$AA$1:$AA$3000,TEXT(NOW()-1,"mm/dd/yy")))

 

[James01]

Hi

Thanks for the reply, I think it will work.

Just to clarify, yes the formula counts the cells having yesterdays date. NOw what I want is that if yesterday was Saturday or Sunday then the answer is 0. This is what the modified formula does, correct?

Thanks again

Cheers

 

[Fazza]

Yes, that is the intention, James. Cheers, F