Conditional Formatting - Using Date and additional value

[Galthark]

I'm trying to use a conditional format to change cell B7 either red or yellow

B7 Value: 4/17/13
C7 Value: 6 - (This means 6 months from the above date we have to turn it back in.

So I wanted to make cell B7 Red when we exceed 6 months from original date and I wanted to make cell B7 Yellow when we are 1 month prior to the total time (6 months).

If this is not possible my other option is to have the dates in cell K-Q:7 change colors (as stated above) using the values in B7 and C7.

I would really appreciate someone's help, I can't find an answer online because I'm using and extra cell that lists the months total we can have an item out


Thanks

[CRIMEDOG]

It is better to attach a spreadsheet not a screenshot
In conditional Formatting you can refer to a specific cell. the $ makes a reference fixed, if you do not use a $ the reference is relative.
so

Please Login or Register  to view this content.

(where K7 is the top left of the range you are formating) means if THIS cell is greater than Cell C7 Then Format.

Please Login or Register  to view this content.

Means if the cell in column K for THIS row is greater than Cell C7 Then Format. This would formatt the whole row in the range. Similarly K$7 would apply to the column.

If you want several colors you apply several Conditional formats to One Range.

[Galthark]

Thanks for the input but I'm staying at hotel and the only thing I could upload was an image. I'm at work now so here is the file.

[Galthark]

I really don't want to highlight the whole row. What I'm trying to do is highlight the date, maybe I didn't explain it well.

Using cell B7 as an example:

We rented a piece of equipment on the 4/17/13. We have rented the equipment for 6 months (this field varies on different contracts) C7. I want my people to be able to see a color change in the original rental date (B7)when they are one month out from the contract ending (yellow). I also want them to know when they are over their contract (red). I tried applying these two conditions and it isn't working. I also want to code it (Green) when they exceed their original contract (7 months or greater using the 6 month example).

I'm trying to decide if it is better to highlight the individual due date cells (K-Q7) instead of the original rental date

Starting with 4/17/13: (Our contracts are on a 28 day period)
In Due Dates, the cell Sep 4, 2013 (O7) will turn yellow if it is that date or later (5 month period)
" " Oct 2, 2013 (P7) will turn red if it is in the (6 month)
" " Oct 30, 2013 (Q7) will turn greed if it is beyond the 6th month (7 months and beyond)

I just need something simple because once I build this sheet people will be inputting rental contract start dates and durations. So, if I can make rules to self populate using these two variable that would be great.

Thanks Again

[CRIMEDOG]

Okay
First I am assuming Column C is the length of Contract.
I would then add a column that is the number of months the equipment has been on site

Please Login or Register  to view this content.

You could use the formula in the conditional formatting - but I think it would be easier in the spreadsheet.
Then the conditonal formatting for B:B would be =$C1-$D1=1 Format set to Yellow
=$C1-$D1<0 Format set to Red

The green highlight is confusing exceed original contract - wouldn't that be the same as over their contract?

[Galthark]

Thanks Crimedog,

Yeah, it was to early for me. I don't know why I put the Green in their, red is fine.

Thanks I really appreciate it.

[CRIMEDOG]

I put it into your spreadsheet so you could see.
On the red format if you add : AND($C6>0, Existing formula ) it will not fill red in the blank rows.

[Galthark]

Thanks,

I really appreciate it!