Conditional Formatting Based On Time Remaining Between Dates

[thlnk]

Hi there,

I've lurked these forums for a while now and have always been able to find and answer to my excel questions.
However this time I'm stuck.

I'm trying to apply conditional formatting to cells based on when the due date is.

For example: "D3" has an initiated date of 23/05/2019, "E3" has a due date of 04/07/2019.

I would like to highlight "E3" as red if it is over due, yellow if 50% of the time has passed before the due date or green if it's under 50% of time remaining.

I've tried to no avail DATEDIF, nested if/and functions, etc and I'm completely stuck.

Any help would be immensely appreciated.

Thank you for your time.

Kyle

 

[DSCfromCFA]

G'day Kyle,

The following will work as you want - two CF rules that assume the natural colour of the cell is green i.e all dates start with more than 50% of the time remaining.

<yearfrac($e$3,$d$3,1) 2
Add this formula first to turn the cell yellow: =YEARFRAC($E$3,TODAY(),1)"<"YEARFRAC($E$3,$D$3,1)/2
then add this rule to handle the change to red: =TODAY()>$E$3

Cheers

shane

edited to fix HTML < issue - ignore " each side of < in the first equation</yearfrac($e$3,$d$3,1)>

 

[DSCfromCFA]

I can't fix the html issue in the previous message as the 10 minute editing window expired, so here it is in coherent this tume (I hope)

G'day Kyle

Give this a try - assuming the cell starts off as green in all cases that can be done without CF, then add

to turn cell yellow: =YEARFRAC($E$3,TODAY(),1) < YEARFRAC($E$3,$D$3,1)/2 (remove spaces from around the < that is a HTML issue with these posts) ,

then add

to turn cell red: =TODAY()>$E$3

 

[thlnk]

Like a charm! Thank you kindly!

 

[DSCfromCFA]

You're welcome, and thanks for the feedback.