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

gplans

New Member
Joined
May 30, 2019
Messages
41
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.

19/07/2019Within warranty
30/11/2018Highlighted cell warranty ended
16/06/2019Within warranty
19/07/2019Within warranty

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

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

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
try =ABS(TODAY()-G1)>=365

30/11/2018 this date does not meet your condition, try 26/06/2018 instead
 
Last edited:

gplans

New Member
Joined
May 30, 2019
Messages
41
try =ABS(TODAY()-G1)>=365

30/11/2018 this date does not meet your condition, try 26/06/2018 instead

This did not work.

It highlighted 15/02/2020 only in these dates. In this case 29/08/2016 was the last day the warranty expired and that should have been the date highlighted.

29/08/2016
16/06/2019
15/02/2020
15/02/2020
15/02/2020

<colgroup><col></colgroup><tbody>
</tbody>

I found how this formula works:

For future dates, higher than today’s date, use B1-TODAY() in the logical test argument.
If the date is smaller than today’s date, use TODAY()-B1.
If you want this to work in both ways, use ABS(TODAY()-B1)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
29/08/2016​
16/06/2019​
15/02/2020​
15/02/2020​
15/02/2020​

so it works

edit:
I just copied your dates from post above and paste into the same range. you need to resize range of Conditional Formatting and adjust G1 to your needs of course

Example CF
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

future date ( 15/02/2020 ) will be highlighted if today()-15/02/2020>=365
 
Last edited:

gplans

New Member
Joined
May 30, 2019
Messages
41
Ok yes yours works but the formula is not working as intended.

An item has a warranty on it and it only last 1 year. The date of the warranty are from:

29/08/2015 to 29/08/2015 - This need to be highlighted
29/08/2019 to 29/08/2020 - This does not need to be highlighted
16/08/2018 to 16/08/2019 - This need to be highlighted

The date shown is the last day the warranty expires.

Thanks for helping.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

you can try this way where no color is the first then color:

screenshot-80.png
 

gplans

New Member
Joined
May 30, 2019
Messages
41
I am not sure what is happening.

I did it your way on a new sheet and it works but when I try it on my sheet it does not.


a>
 

Watch MrExcel Video

Forum statistics

Threads
1,127,143
Messages
5,622,977
Members
415,943
Latest member
JakeG

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
Top