Help change cell colour depending on date in another cell

Hi all,

I am trying to create a sheet in XL 2010.

In Column F each cell will contain a date (differnet from other cells in that column) when inspection was last done.

Column G is when the weekly inspection is due.

Column H is when bi-weekly inspection is due.

Column I is when monthly inspection is due.

Column J is when 6 monthly inspection is due.

I need a formula to change the colour of cells G, H, I & J when each inspection is due.

I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.

For example if cell F3 has a date of January 1 2013 then on January 8 2013 cell G3 turns red then on January 10 2013 the cell returns back to normal.

Thanks

Select cell F2, select Ribbon Tab Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format.
Paste this into the formula box: =AND(F2>=TODAY(),F2<TODAY()+3)
Select the Format button and choose red.

This should cause the cell F2 to be highlighted in red when the date is due and will stay like this for 2 days afterwards.

Thanks mc84

But I dont think this is the exact answer I am looking for or at least not sure how to work it.

I am needing, for example, cell G2 to change colour based on cell F2 date. I have attached the worksheet for you to have a look with some notes.

Thanks for the help!!

thanks mc84

Thanks for the help. This is not exactly what I am after but very close. After thinking last night I have thought of another requirement I hope can be doen in XL.

First Ill explain a bit more what I am trying to do.

Based on your table:

A2 is the date of the last inspection carried out.
B2:E2 is cells to indicate when the next inspection is done based on the indicated time period
B2:E2 will need to be left blank as an inspection will be due the following week, bi-week etc

The formula would need to include both the date entered in A2 and the system date.

As this sheet is populated it will be too much work to continually add in the dates in Columns B through E. The idea is what when opened I can easily view what inspections are due that day, having the cell stay coloured for two days will give me a grace period to organise the inspection. It basically acts as am alert calendar.......if possible.

Another thing I am hoping to add in the formula is something to have it repeat depending on the time period. For example A2 has a date of Jan 1. On January 8 the cell would turn yellow and remain yellow until the 10th and then turn back blank. The formula would then repeat its self on the 15th remain yellow until the 17 and return blank and so on an so on.

I am only needing the one formula for cell B2 and then I can go from there.

Hope this doesnt seem to confusing.

here is the sheet with some remarks.

cheers

Hi nubehaviour,
Do you mean 1 month = 4 weeks = 28 days?
And 6 months = 168 days?

You can use Conditional Formatting with formulas:
F3: =MOD(TODAY()-E3,7)=0
G3: =MOD(TODAY()-E3,14)=0
H3: =MOD(TODAY()-E3,28)=0
I3: =MOD(TODAY()-E3,168)=0

Choose color you want for each.

If a month isn't 28 days and 6 month isn't 168 we use other formula.

Thank you huuthang_bd.

nubehaviour - please enter the formula below in Conditional Formatting for cell F3 to see if this is what you are after.

Please Login or Register  to view this content.

It should change colour when the difference between todays date and the date in E3 is a multiple of 7. And remain coloured for two days after.

If that formula works for you, let me know and I can supply the formulas for the other cells.


(A tip for testing. Replace the TODAY() references in the formula with a cell reference e.g. D3 and then type different dates in D3. It makes for easier testing rather than changing the system clock).