Use of TODAY() and DAYS Function in Conditional Formating

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am trying to get a cell 'Target Date' to change from green to amber to red as the days countdown towards that date.
As the days count down cell format is green when there is still 20 days still to go. Changes to amber when only 10 days to go and red when only five days to go.
When a date is entered into 'Completion Date' cell, the count stops and the colour of the 'Target Date' cell stays as is based on the number of days passed when the 'Completion Date' cell had a date entered.
Here is what I have written, but doesn't work for the green formatting

=IF($G$8 -TODAY(),"",IF($G$8-TODAY(),"DAYS>=20", "False"))
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
How about
=AND(G8>=TODAY()-20,G8<=TODAY()-10)
 
Upvote 0
It didn't work, but can I talk through the logic of my understanding of this statement in english speak

=AND(G8>=TODAY()-20,G8<=TODAY()-10)

When G8 is greater than todays date less 20 days and when G8 is less than or equal to todays date less 10 days.
How would this be applied to a Green, Amber Red count down as the target date gets closer and closer with no completion date entered.
Are 20 and 10 automatically recognised as DAYS or should that specified?
G8 is recognised as a date based on the formatting I have used.
 
Upvote 0
You have understood correctly :)

+Fluff New.xlsm
G
811/06/2020
912/06/2020
1013/06/2020
1114/06/2020
1215/06/2020
1316/06/2020
1417/06/2020
1518/06/2020
1619/06/2020
1720/06/2020
1821/06/2020
1922/06/2020
2023/06/2020
2124/06/2020
2225/06/2020
2326/06/2020
2427/06/2020
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G8:G24Expression=AND(G8>=TODAY()-20,G8<=TODAY()-10)textNO


Are you sure that your dates are real dates & not text?
 
Upvote 0
Great thank you, that is almost there but not quite.
I'm a bit confused and probaly haven'y explained myself well enough
The above formula based on the target date being entered back on the 12/06 it will stay green for 10 days then no colour.
This doesn't do quite what I want
Using the 12/06 as a Target date in the past that has elasped now that we are Today 02/07 the number os days between then and now is 20 so 'Target Date' cell is green.
Maybe the formula should be + 20. Its hard to simulate the effect of the TODAY function until you actually get there.
How will this formula turn the Target date cell amber tomorrow when 20 days have elapsed. I am trying to create a traffic light Green Amber Red count down.
Between 20 -10 still left to complete the task Green
Between <10 and >5 days left to complete the task Amber
With less than 5 days left from Target date to complete the task Red
I do not think the above formula will do this, and probly becuase of my poor explaination for what I am trying to achieve.
I hope the above makes it clearer.
Thank you as always for your help
 
Upvote 0
Maybe
+Fluff New.xlsm
G
828/06/2020
929/06/2020
1030/06/2020
1101/07/2020
1202/07/2020
1303/07/2020
1404/07/2020
1505/07/2020
1606/07/2020
1707/07/2020
1808/07/2020
1909/07/2020
2010/07/2020
2111/07/2020
2212/07/2020
2313/07/2020
2414/07/2020
2515/07/2020
2616/07/2020
2717/07/2020
2818/07/2020
2919/07/2020
3020/07/2020
3121/07/2020
3222/07/2020
3323/07/2020
3424/07/2020
3525/07/2020
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G8:G35Expression=G8<=TODAY()+5textNO
G8:G35Expression=G8<=TODAY()+10textNO
G8:G35Expression=G8<=TODAY()+20textNO
 
Upvote 0
Maybe
+Fluff New.xlsm
G
828/06/2020
929/06/2020
1030/06/2020
1101/07/2020
1202/07/2020
1303/07/2020
1404/07/2020
1505/07/2020
1606/07/2020
1707/07/2020
1808/07/2020
1909/07/2020
2010/07/2020
2111/07/2020
2212/07/2020
2313/07/2020
2414/07/2020
2515/07/2020
2616/07/2020
2717/07/2020
2818/07/2020
2919/07/2020
3020/07/2020
3121/07/2020
3222/07/2020
3323/07/2020
3424/07/2020
3525/07/2020
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G8:G35Expression=G8<=TODAY()+5textNO
G8:G35Expression=G8<=TODAY()+10textNO
G8:G35Expression=G8<=TODAY()+20textNO

I have added the following to the CF
=IF(G10="",TODAY(),"")

1593700693364.png

So that when cell G10 does not contain a date then it will appears empty

1593700838341.png


Thanks again for your help.
I think my issue is now resolved :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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