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!
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!