Conditional Formatting on Cell that is outside the 365 days warranty end date

[gplans]

I am trying to set some conditional formatting on cell that are outside the 365 days warranty end date that is entered into the cell.

[TABLE="width: 101"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 101"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 296"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]19/07/2019[/TD]
[TD]Within warranty[/TD]
[/TR]
[TR]
[TD]30/11/2018[/TD]
[TD]Highlighted cell warranty ended[/TD]
[/TR]
[TR]
[TD]16/06/2019[/TD]
[TD]Within warranty[/TD]
[/TR]
[TR]
[TD]19/07/2019[/TD]
[TD]Within warranty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Cant seem to find anything that matches my criteria. Any help thanks.

 

[sandy666]

delete first two rules and re-enter them again (on the first and second positions)
check if your dates are real dates not a text dates

 

[gplans]

delete first two rules and re-enter them again (on the first and second positions)
check if your dates are real dates not a text dates

So I re-did the lines like you said and still it does not work 100%. They are dates but the dates are derived from a formula.

BTW I did use the formula =ABS(TODAY()-r3)>=365 not the one on the screen shot.

 

[sandy666]

could you post a link to shared excel file with this column only? (OneDrive, GoogleDrive, DropBox or any similar)

btw. look at the first rule on your last picture =R1048521>TODAY()

 

[gplans]

could you post a link to shared excel file with this column only? (OneDrive, GoogleDrive, DropBox or any similar)

btw. look at the first rule on your last picture =R1048521>TODAY()

Ok still no luck. see my file link.

https://www.dropbox.com/s/2cc7lyxyq3p17cs/OBS MASTER SPREADSHEET.xlsm?dl=0

 

[sandy666]

delete all unnecessary CF rules and stay with this:

Excel file

 

[gplans]

delete all unnecessary CF rules and stay with this:

Excel file

Ok so I need all the CF rules for the rest of the spreadsheet that is missing. I have added your formula and you can see that it only works part of the time. The ones that are highlighted in orange it does not.

https://www.dropbox.com/s/gupe8w4qurtv8o1/s666-CF-OBS MASTER SPREADSHEET.xlsm?dl=0

 

[sandy666]

I don't understand what are you trying to achieve
As I said what must be the first and what must be the second
you are loosing or set inappropriate ranges
in your first linked file I see only one different rule with IF, the rest is unnecessary beyond the ones you see here

all values (CFs) outside the table, were only for validation of formulas

try to do everything step by step with proper settings

and read this first (everything!):

• Add, change, or clear conditional formats
• Manage conditional formatting rule precedence

 

[sandy666]

if you want to use DATEDIF() function you must be aware of how it works and what errors and why they occur there

 

[gplans]

Ok, I am no excel genus as you can see. I have followed you instructions but I still can't even get it to work on your sheet. It seems to work sometimes bot other times it does not.

The reason why my If statement is long.

IFERROR(IF(VLOOKUP(B79,'OBS Job List'!$A$2:$D$53,4,FALSE)=0,"",VLOOKUP(B79,'OBS Job List'!$A$2:$D$53,4,FALSE)),"")

Is if there is no date then it will not bring up an error in the cell. I hope that it was what you were referring to regarding my IF statement.

Thanks again for looking. I did go through the tutorials but as you can see the mystery has not been solved.

 

[sandy666]

I hope that it was what you were referring to regarding my IF statement..

No, it was formula in CF rules: =IF(#REF!="","",DATEDIF(#REF!,TODAY(),"d")>7)

I'll look at CFs later