Help change cell colour depending on date in another cell

[nubehaviour]

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

[mc84excel]

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.

[nubehaviour]

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

[mc84excel]

Try the following steps in the uploaded workbook to see if this is what you require:

Select columns F:I
On the Ribbon tab 'Home' -> 'Conditional Formatting' -> 'New Rule' -> 'Use a formula to determine which cells to format'.
Copy and paste the line below into the formula box 'Format values where this formula is true:'
=AND($E1>=TODAY(),$E1<TODAY()+3)
Select the Format button and choose the red colour.
Select OK & then OK again to return to Sheet 1.

I have tested the above steps using various dates in the E column. When the system date is equal to or 2 days after the date recorded in column E then cells F:I for that row will highlight in red. I understand that this is what you are after - the cell colour change to red on the due date, remain changed for two days and then return to blank?

[nubehaviour]

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.

[mc84excel]

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.

The cells you refer to (A2, B2:E2) in the Preservation Calendar1.xlsx do not contain the content you describe. To help me understand what you are trying to achieve, could you please upload an updated excel file with a few quick notes? Thank you.

(Regarding the repeating dates proposal - I recommend that you start a new thread for this request).

[nubehaviour]

here is the sheet with some remarks.

cheers

[huuthang_bd]

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.

[mc84excel]

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.

=OR(MOD(TODAY()-E3,7)=0,MOD(TODAY()-E3-1,7)=0,MOD(TODAY()-E3-2,7)=0)

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).