I have been reading thread all day and trying different ones to see if they work for what I need, no luck.



OK, each cell will have a different date. I need the cells to automatically change colors when they reach one of the parameters.

<90 days from the date's annual date will turn yellow
<60 days from the date's annual date will turn amber
<30-past due from the date's annual date will turn red
>91 days it will remain green

Please let me know if you need more information

Please. Let's put all your conditions in the same terms.

Y: CellDate<Today()+365-90
A: CellDate<Today()+365-60
R: CellDate<Today()+365+30
G: CellDate>Today()+91

Please verify these and we can proceed.

I have done this but all the cells are coming up yellow. Unless I have entered them wrong. I have entered the following dates just to check the conditional formatting.

A1: 3/4/14
A2: 4/4/14
A3: 5/4/14
A4: 6/4/14

With conditional Formatting:

cell value less than =today()+365-90 yellow
cell value less than =today()+365-60 amber
cell value less than =today()+365+30 red
cell value greater than =today()+91 green

This is correct?

You have to make sure you have the order right for the conditions. Also I'm not entirely clear on those equations as I've had a few adult beverages. Attach your file and I'll take a gander tomorrow.

I put them in the order that you have and I greatly appreciate looking at it for me. This will once done make my life a lot easier. Thank you

Attached Files

Book1.xlsx (8.9 KB, 11 views)

There seem to be some inconsistencies in the logic and I just can't make it out. It's probably my interpretation.

Please give me the first of every month from 1/1/14 through 12/1/15 and indicate how the schema should format them.

As a value can be at the same time larger than today()+90 and smaller than today()+365-90 the green and yellow conditions are not possible. You will have to recheck your logic .
Or is it that it would be green if the value is LESS than today+91?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I have made corrections inside the form. thank you for the help let me know if there is anymore questions.

Attached Files

Book1.xlsx (9.3 KB, 13 views)

I am at a loss understanding the logic. Why are some dates from last year green and others not?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Because they have been completed within the last 365 days. I am trying to get the cells to change colors based on when their anniversity date is.

Ok the system that we have is you will enter the certificate date, they are good for one year. Therefore, I need the dates to change colors when it approaches for them to be renewed.

Give this a try.

Attached Files

Book1 (1).xlsx (8.9 KB, 10 views)

gebobs, Thank you so much. I am going to take this to work today to see if it will work in my spreadsheets there. Where did you learn all this about excel?

Upwards of 30 years working on spreadsheets starting with ol' Lotus 1-2-3, then Quattro Pro, Microsoft Works, Excel and now Google sheets.

Gebobs,

This threw me for a wrench here. When I copy the conditional formatting over to the new sheet it does not work like it did in the example sheet. I am posting the working sheet that I am working on. The far left column will have names and the top with have their certificates. If you wouldn't mind once you fix this can you explain to me why it would not copy over that way I know how to move it around different workbooks? Thank you

Attached Files

Working Sheet.xlsx (13.1 KB, 10 views)

* Select B2.
* Conditionally Formatting>Manage Rules
* For applies to, you have just =$B$2 for each rule.
* They should apply to all the cells in your table in B, i.e. =$B$2:$B$67

Easy peasy. Keep the $s in the apply ranges. More on that later.

* Select the first rule.
* Click Edit Rule.
* The equation as copied is =TODAY()>DATE(YEAR($A2)+1,MONTH($A2)-1,DAY($A2))
* Change all $A2 to $B2.
* Click OK.
* Repeat for the remaining rules.

The reason this happened is because I left some absolute reference markers, $, in the equations. I only removed the $s in front of the row reference in case you wanted to copy the formatting down. I didn't consider you would be copying across (from A to B). Thus, when you copied the equations from A in the sheet I posted to B in your sheet, the formulae did not change likewise. My bad.

I took care of it for you, but you might want to go through this on your own as an exercise to understand how this works.

And here's a bit of homework for you to hone your conditional formatting skills. There is one date, 4/1/15, that has no formatting. I suspect that this is because its anniversary date is more than a year away. See if you can modify the "green" rule to accommodate this date.

Attached Files

Working Sheet2.xlsx (13.3 KB, 21 views)