PDA

View Full Version : [SOLVED] Highlighting cells different colors for pending expiration date



mj8057
10-03-2013, 11:33 AM
Hello All,

I hesitated to post this on here because I am not sure if VBA or worksheet formula is the best option for this and it seems ridiculously easy compared to the other posts for help I see in here (feel a bit like a dumb#%#%) but here goes:

I have excel 2010 and have played around with a few "formulas" via conditional formatting and other functions and cannot seem to get the right one. Or maybe I am just entering them wrong. This includes the examples below:

1) Conditional Formatting: DATEDIF function
2) [less than] =DATE(YEAR(NOW()),MONTH(NOW())+10,DAY(NOW())),etc.
3) Conditional Formatting: cells contain only, =NOW()+30

The whole point of this is to track when we installed a part and give warnings for when we need to have it reinspected/reinstalled.

So every time a new part is installed it is put on the spreadsheet with the date in the A column.

The part will have to be re-inspected/serviced/replaced after a quarter (3 months)

91 days (or 90) would be the expiration date. So if the date was Jan 1s then March 31st it would expire. Jan 2 would have an April 1 expiration and so on.

Two weeks from this expiration date would be 77. So 77 days AFTER the date in A column it would need to be Yellow.

Two days before the expiration date would be 89 days. So 89 days after the date in A column it would need to be Orange

10 days after the expiration date it would be 101 days after the date in A column and need to be Red.

So to sum up.

Date in Column A +77 days would have it turn Yellow
Date in Column A + 89 days would have it turn Orange
Date in Column A +101 days would have it turn Red

It does not matter how many cells in the row change color.

I have attached an "anonymised" example. Everything in the example is exactly as in the sheet with the serial numbers and product descriptoins changed to "y" and whatnot. The dates are exactly the same as the original.

Thank you for any help or pointers you can give me and if I can provide any more information or clarify something please let me know.

Thank you!

david000
10-03-2013, 09:27 PM
You could select the whole table and enter these formulas for each category in the conditional format dialog thing. The dollar sign in front of the "A" is telling the formula to look in column "A" only for the date.




So to sum up.
Date in Column A +77 days would have it turn Yellow
Date in Column A + 89 days would have it turn Orange
Date in Column A +101 days would have it turn Red


=IF(DATEDIF($A6,TODAY(),"d")>=77,1,0)
=IF(DATEDIF($A6,TODAY(),"d")>=89,1,0)
=IF(DATEDIF($A6,TODAY(),"d")>=101,1,0)

mj8057
10-04-2013, 02:25 PM
Thank you very much. Works like a charm.

Thread marked solved.

THANKS AGAIN!:hi: