Cell color change and days left

mikemwa

New Member
Joined
Nov 9, 2006
Messages
9
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

New Member
Joined
Nov 9, 2006
Messages
9
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,368
Messages
5,595,755
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top