Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

jms479

New Member
Joined
Oct 7, 2019
Messages
4
I am doing project management and schedule planning and am looking for method of easily highlighting cells when something is overdue or soon-to-be-due.

To simplify my question, say I have two columns, one with date's (expected task finish date) and one with % complete:
(Example: D2: 10/06/2019; E2: 85%)
(Example: D3: 10/12/2019; D3: 75%)
(Assume today's date is 10/07/2019).

How can I use conditional formatting so that any cell in column D would have different background/font color based on the planned finish date in relation to today's date and in relation to task completion percentage?

The criteria we'd like to use:
1) If task target finish < today's date AND % < 100%, turn cells red
2) If task target finish is WITHIN 2 weeks of today's date AND % < 90%, turn cells orange.

Any help with this where I could use formatting and not need some other column would be so amazing!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Hi and welcome to the board.

The way I learned it is conditional formattings expecting a TRUE or FALSE evaluation from you.

If the formula you describing the conditional formatting is true, then it will do the formatting if you evaluate it to false it won't.

In the formula below you can see this true or false represented by the 1,0 at the end of the IF formula.

If you want to evaluate an IF formula for multiple conditions, then you have to use the AND or the OR formula.
These guys will turn your IF formula into a multiple "question" formula.

This is how I construct my conditional formulas:
Down below, are your data to evaluate (Column D:E)
Then the conditional formulas in F2 and F3.

You construct your formulas next to the range you want to format then you copy this formula, from the formula bar itself to the conditional formatting.

DEFG
Red
Orange

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]06/10/2019[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20/10/2019[/TD]
[TD="align: right"]89%[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(AND(D2<NOW(),E2<1),1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IF(AND(D3<NOW()+14,E3<0.9),1,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Please note that I did not lock the formulas in any way, if you need to cover a bigger range then you will need to lock the formulas.
 
Upvote 0
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Welcome to the MrExcel board!

Try these Conditional Formatting rules. They need to end up in the correct order.

Excel Workbook
DE
1Date%
26/10/201985%
312/10/201975%
425/12/201910%
523/09/2019100%
615/10/201998%
CF Tasks
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D21. / Formula is =AND(D2<1)Abc
D22. / Formula is =AND(D2<1)Abc
 
Last edited:
Upvote 0
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Thank you so much for your help on this!


Hi and welcome to the board.

The way I learned it is conditional formattings expecting a TRUE or FALSE evaluation from you.

If the formula you describing the conditional formatting is true, then it will do the formatting if you evaluate it to false it won't.

In the formula below you can see this true or false represented by the 1,0 at the end of the IF formula.

If you want to evaluate an IF formula for multiple conditions, then you have to use the AND or the OR formula.
These guys will turn your IF formula into a multiple "question" formula.

This is how I construct my conditional formulas:
Down below, are your data to evaluate (Column D:E)
Then the conditional formulas in F2 and F3.

You construct your formulas next to the range you want to format then you copy this formula, from the formula bar itself to the conditional formatting.

DEFG
Red
Orange

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]06/10/2019[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20/10/2019[/TD]
[TD="align: right"]89%[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=IF(AND(D2<now(),e2<1< font="">),1,0</now(),e2<1<>)[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=IF(AND(D3<now()+14,e3<0.9< font="">),1,0</now()+14,e3<0.9<>)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Please note that I did not lock the formulas in any way, if you need to cover a bigger range then you will need to lock the formulas.
 
Upvote 0
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Thank you so much for your help on this!

Welcome to the MrExcel board!

Try these Conditional Formatting rules. They need to end up in the correct order.

CF Tasks

DE
Date

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]%[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]6/10/2019[/TD]
[TD="align: right"]85%[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffc000]#ffc000[/URL] , align: right"]12/10/2019[/TD]
[TD="align: right"]75%[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]25/12/2019[/TD]
[TD="align: right"]10%[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]23/09/2019[/TD]
[TD="align: right"]100%[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffc000]#ffc000[/URL] , align: right"]15/10/2019[/TD]
[TD="align: right"]98%[/TD]

</tbody>

Conditional formatting
CellNr.: / ConditionFormat
D21. / Formula is =AND(D2<TODAY(),E2<1)

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]Abc

<tbody>
D22. / Formula is =AND(D2<TODAY()+14,E2<1)

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffc000]#ffc000[/URL] "]Abc[/TD]

</tbody>
[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Do I need to do something to close a thread once my issue is resolved? Want to make sure I help keep this board clean and abide by the best practices and preferences of this community.
 
Upvote 0
Re: Help with Formula to Conditionally Format Cells where Planned Finish Date < Today's Date AND % Complete is < 100%

Thank you so much for your help on this!
You're welcome.


Do I need to do something to close a thread once my issue is resolved?
Just a reply to say you are satisfied, like you have done. We don't formally close threads in this forum as it may well be that somebody else comes along afterwards and posts an even better solution. :)


Want to make sure I .. abide by the best practices and preferences of this community.
One thing to be careful of is fully quoting long(ish) posts like you did in posts 4 and 5. They tend to make the thread harder to read/navigate. Just quote smaller relevant parts only or just enough so readers know who or which post you are referring to.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top