Conditional Formatting for Leases

Question

I need help!

I'm working on a spreadsheet to track way too much; I work for a non-profit and need to track client info. One of the things I need to track is their lease. I'd like to have conditional formatting for when the lease expires. Something like:

Green: 365days - 180days

Yellow: 179days - 75days

Red: 76-0

For the life of me I can't seem to get the conditional formatting to work for this. I'd use different software, multiple spreadsheets, or something different all together but some higher ups are basically luddites and struggle with adapting to new technology.

Any help would be greatly appreciated!

Answer 1

Hi Gr8fulDude. I am an Excel user like you.

I was able to get the result you described by using the BETWEEN formatting under Conditional Formatting>Highlight Cell Rules. However, there was one hitch. I used the TODAY() formula + the appropriate number of days for the beginning and ending data, but when I clicked OK it didn't work because Excel added quotation marks around the entries. I then opened up Conditional Formatting>Manage Rules, selected the rule, and then clicked on Edit Rule and removed the quotation marks as below:

It worked fine then.

Reply if you have additional questions or more information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Hi

Please, try the following steps

1. Select your entire Expire Date column range
2. Go to Conditional Formatting \ New Rule \ Use a formula to determine...
3. and use the formula

For YELLOW warning (3 month to expire date = 0 to 90 days)

=AND(DAYS($C2,TODAY())>0,DAYS($C2,TODAY())<=90)

For GREEN warning (more then 3 month)

=DAYS($C2,TODAY())>90

For RED (lease has expired)

=DAYS($C2,TODAY())<=0

4. Select the color format of your preference.

IMPORTANT NOTES

1) Adapt correctly the ranges in the CF formulas according to your real scenario

In the sample picture above the first cell of the expire date Range is cell C2 hence the formulas reference that cell

Change yours accordingly.

I hope this helps you

Regards

Jeovany

Answer 3

This kind of worked. I tried the method you suggested, but I couldn't get all the conditions to work. After some playing around, I was able to get what I needed...I think. We'll see how well it works.

I don't think I explained my needs clearly enough, so that's my fault. Here's what I was looking for:

I'm looking for a way to track when a housing client's lease is up and they can move again. So if the client has a green box for their lease date, their lease is still good, if the client has yellow they are nearing their lease ending, if the lease date is red, their lease has expired.

I ended up needing to change the "+" on the yellow category to "-" and add a second green category that uses "-" instead of "+".

Oh, and I couldn't get the "today()" formula to work, so I added a "today's date" cell on the spreadsheet and used that for reference.