Hi im trying to get excel(2010) to change the colour of a cell once a period of

Question

Hi

im trying to get excel(2010) to change the colour of a cell once a period of time has passed.

I have,

B1 showing the date the project was entered (in d/m/y)

C7 showing the deadline date (in d/m/y)

N7 showing today's date ( I used =today() to get excel to put today's date in)

what I would like is for N7 (which is today's date) to stay green until it is one week older than the date shown in B1. then change again to red once N7 is two weeks older than B1.

any help would be great

thanks

in advance!

Explanation / Answer

Firstly, change the fill colour of the cell to green.

Then, you will need to add conditional formatting to cell N7 in two steps as follows:

Select N7. Click on the Home ribbon and Conditional Formatting. Select New Rule.

Select 'Use a formula to determine which cells to format. Then, in the formula box, type:

=($N$7-$C$7)>=7

Click the Format button and select the desired formatting, i.e click Fill and select, for example, yellow.

Hit OK a couple of times and then repeat the process, putting the formula in as =($N$7-$C$7)>=14, and select the fill colour as Red.

Excel will check the validity of the most recently added condition first, so will highlight Red if 14 or more days from deadline date, Yellow if 7 or more days, or leave green if under 7 days after deadline.

Regards!

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.