# Count if formula with workday?

1. ## Count if formula with workday?

My current formula is this:

=COUNTIF(\$A\$5:\$A\$336,TODAY()-1) - where column A has dates (Monday thru Saturday dates entered Monday through Friday).

But if yesterday was a Sunday, my formula is this:

=COUNTIF(\$A\$5:\$A\$336,TODAY()-2)+COUNTIF(\$A\$5:\$A\$336,TODAY()-3)

I have to pass this spreadsheet on to someone with very little formula (or excel) knowledge. Explaining how to change the formulas every Monday and change it back on Tuesday is not an option. What is a more permanant solution?

Ambrosia  Register To Reply

2. ## Re: Count if formula with workday?

I can't quite figure out what you're trying to calculate.
However, I *think* the WORKDAYS.INTL function (available beginning with Excel 2010) will do what you want.
Is that something you can work with?  Register To Reply

3. ## Re: Count if formula with workday?

do an IF statement to test what day of the week you are in

=IF(WEEKDAY(TODAY(),2)=1, COUNTIF(\$A\$5:\$A\$336,TODAY()-2)+COUNTIF(\$A\$5:\$A\$336,TODAY()-3), COUNTIF(\$A\$5:\$A\$336,TODAY()-1) )

tests to see if today is a monday and if it is does your monday formula - if not does the normal formula  Register To Reply

4. ## Re: Count if formula with workday?

I'm not familiar with it, but I will research and learn about it. Thank you for your suggestion!   Register To Reply

5. ## Re: Count if formula with workday?

I don't have any way to test the other half of it until Monday, but it returned the same results for today. Thank you for the suggestion!   Register To Reply

6. ## Re: Count if formula with workday?

to test , you could tel the formula to change on a Thursday

so change the
WEEKDAY(TODAY(),2)=1

so instead of looking for 1 as a Monday , change to
WEEKDAY(TODAY(),2)=4
4 Which is a Thursday - so now it will skip a day

so a bit of a test  Register To Reply