Options
edited 11/02/20

Hi, I'm looking for some help with the below.

For the most part I'm able to make the formula work, however I'm unsure how to account for Friday. For example today is Monday, but the formula only reports Sundays data (nonworkday). Also all of my formulas are in the Sheet Summary, as I'm trying to prevent any need for additional columns if possible.

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), [Critical Ops.]:[Critical Ops.], ="Yes")

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), Status:Status, ="Yes", [Critical Ops.]:[Critical Ops.], "Yes")

Edit To add the populated number from the formula would then populate into a dashboard, so it would need to be automatic instead of specific to individual rows/cells and manually inputting the information in.

• Employee
Options

Would you need to account for if Today is Sunday at all (to look back 2 days to Friday), or do you just need it to be assuming Today is Mon-Fri?

The way I would personally figure this out would be to use an IF statement. This way you don't need a helper weekday column at all.

The IF statement would go, If Today is Monday, then do the calculation with TODAY(-3), otherwise, do the calculation with TODAY(-1)

Try this:

=IF(WEEKDAY(TODAY()) = 2, COUNTIFS([Due Date]:[Due Date], TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes"), COUNTIFS([Due Date]:[Due Date], TODAY(-1), [Critical Ops.]:[Critical Ops.], "Yes"))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

UPDATE To hopefully make this more achievable, I added a Weekday column that correlates with the due date (i.e. IF Due Date = 02NOV2020, THEN Weekday = 2)

With that said I would need my formula to have an OR statement combining the below formulas

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), [Critical Ops.]:[Critical Ops.], ="Yes")

=COUNTIFS([Due Date]:[Due Date], =TODAY(-3), Weekday:Weekday, AND(@cell = 6), [Critical Ops.]:[Critical Ops.], ="Yes")

Both work as separate formulas, however when I try to combine them it either populates as 0 or gives me an error.

• Employee
Options

Would you need to account for if Today is Sunday at all (to look back 2 days to Friday), or do you just need it to be assuming Today is Mon-Fri?

The way I would personally figure this out would be to use an IF statement. This way you don't need a helper weekday column at all.

The IF statement would go, If Today is Monday, then do the calculation with TODAY(-3), otherwise, do the calculation with TODAY(-1)

Try this:

=IF(WEEKDAY(TODAY()) = 2, COUNTIFS([Due Date]:[Due Date], TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes"), COUNTIFS([Due Date]:[Due Date], TODAY(-1), [Critical Ops.]:[Critical Ops.], "Yes"))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

My apologies for the delayed response, but this formula worked perfectly thank you so much

• Employee
Options

No problem at all, I'm glad it worked for you! 🙂