# 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

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?

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

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!

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!

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1