Checkbox if Date Past Due AND <100% Complete OR In Next 10 Days AND <100% Complete

Hello,

I am trying to create a formula that will automatically check a box if a due date is past due AND % Cmplt is not 100% OR due date is in the next 10 days AND % Cmplt is not 100%. Basically, I will pull the checkboxes items into a report for a dashboard to provide visibility into past due items and upcoming items.

Is that possible?

Find more posts tagged with

Formulas

Comments

Paul Newcome

Try something along the lines of

=IF(AND([% Complete]@row < 1, [Due Date]@row <= TODAY(10)), 1)

RGeet

Thank you Paul. That worked perfectly.

You taught me something about IF(And). I assumed an IF/And statement meant both conditions have to be met so I was thinking I had to incorporate OR somewhere in a formula. Also, is it correct for me to read "<= Today(10) as "less than today times 10 days)?

Paul Newcome

Happy to help!

And yes. The AND function requires all criteria be met. The reason we could use it for this is that both sets of criteria had the % Complete being less than 1 in common. Since both have that in common, and both are looking at dates, we were able to simplify.

% Complete < 1 works for both of your conditions.

Any dates that are past due are going to be less than today anyway as well.

So all we had to do was establish the commonalities, and we were able to not worry about the OR function. If you had different criteria though, we may have needed the OR function. It just so happens that in this particular instance we didn't.

TODAY(10) would be today PLUS 10 days. TODAY(-10) would be today MINUS 10 days. Any time you see a number inside of a TODAY function, you can essentially write it outside and get the same results.

TODAY(10) = TODAY() + 10 (positive ten)

TODAY(-10) = TODAY() - 10 (negative ten)

It's really just a matter of preference.

RGeet

All of that makes sense and thank you for taking time to explain. Helps a lot.

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…