Cell color change and days left

[mikemwa]

I have of a spreadsheet that I’m working on. I have a few things I want to do with it.
1. If the last date under the used columns are 30 or more days prior to today’s date then I want to change the fill color in the qty cell to green.
2. In the days left column I want to display how many days are left before I can use another certificate. You can only use 1 certificate every 30 days. When I use a certificate I put the date it was used under the used headings. I have an image of the spreadsheet and the actual file here:
http://mikemwa.googlepages.com/excel
How do I go about doing this.

Thanks,
Mike

 

[ravishankar]

Hi
In col G insert the formula =F1-today() and drag it till the end of data to get no of days left. To highlight qty used green, Which "used" column has to be used.
Ravi

 

[mikemwa]

Right now it varies because it's the last one filled in on the right. Although I might need to change that if it can't be done that way.

 

[xenou]

It looks like you could effectively use some variation of a MAX() formula:
Max(I3:P3) gives you your last used date, right?
So, (Max(I3:P3)+30)-Today() would be days left (careful to format as general here)...with negative numbers showing overstepped.

The same might work for formatting...Conditional Formatting | formula is... | =And(I3=Max($I3:$P3),I3+30<=Today())

I haven't tested this much...hope it helps.