Formula for tasks/Sheet Summary

Brandon Mitchell

So I'm at another roadblock so bare with me... I have a current formula that tracks the prior day's target tasks and a formula that tracks the prior day's completed tasks. However I want to incorporate my overdue tasks in these formulas as they still need to be accounted for. I was able to add my overdue tasks to my daily target tasks and daily completed tasks formulas but I can't quite figure out what I'm missing to do the same accounting.

The 2 formulas below are the ones in question.

Prior Day's Critical Ops. Target

=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.], "No"))

Prior Day's Critical Ops. Actual

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

The 2 formulas below are what I'm using to track as Daily Tasks (including completed overdue tasks).

Daily Critical Ops. Target

=COUNTIFS([Due Date]:[Due Date], <=TODAY(), [Critical Ops.]:[Critical Ops.], ="No", [Completion Date]:[Completion Date], "")

Daily Critical Ops. Actual

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

I'm honestly not certain if it's possible but maybe there's something I'm not seeing or maybe there's a better method. The data from this would translate to a dashboard widget. For the Daily Critical Ops. it works perfectly in a Pie Chart widget and I would like the Prior Day Critical Ops. to do the same.

Please let me know your thoughts and/or suggestions.

Mark Cronk

Hi @Brandon Mitchell ,

Your prior days target would be those due on the prior day + those overdue the prior day. The OR statement finds those that are overdue ( no completion date) or those that were completed after the prior day.

=IF(WEEKDAY(TODAY()) = 2, COUNTIFS([Due Date]:[Due Date], =TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes") + COUNTIFS([Due Date]:[Due Date], <TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes", [completion date]:[completion date], OR(isdate(@cell)=0), >Today(-3)),

COUNTIFS([Due Date]:[Due Date], TODAY(-1), [Critical Ops.]:[Critical Ops.], "No") + COUNTIFS([Due Date]:[Due Date], <TODAY(-1), [Critical Ops.]:[Critical Ops.], "Yes", [completion date]:[completion date], OR(isdate(@cell)=0), >today(-1))

Is that what you're looking for?

Mark