Database

[doncop]

Ok, I am EXCEL ignorant, my dillema is as follows: I need cells that change color based on an expiration date x amount of days out from the date entered, not "TODAY''s date. I need three different colors based on three different dates. this needs to happen in every cell I enter a date in...and go...

 

[Desu Nota from Columbus]

can you provide an example of the data?

I dont quite understand how the data will be set in excel.
Is there a date in column A, and then other dates in columns(B,C,D) that you want change colors based on your expiration date x?

 

[Desu Nota from Columbus]

If my question is correct: There is a date in A1, b1, c1, d1 and A1 is the reference date. You want b1, c1, and d1 to change colors if x1, x2, and x3 days have gone by, respectively.


Substitute your desired number for x1, x2, and x3 in the following formulas in conditional formatting menu and set your formats.


The answer you are looking for is conditional formatting.

1. Column B Condition 1:

Formula is: =B1>(A1+x1)

2. Column C Condition 1:

Formula is: =C1>(A1+x2)

3. Column D Condition 1:

Formula is: =D1>(A1+x3)

 

[doncop]

yes and no...I actually need all that to happen in one cell. what I have is, for example, John Doe qualified on 1 jan 01(green cell), his quals expire in 1 jan 02, which will turn red if qualified date is not updated. In addition, it needs to turn yellow at 90 days out from expiration. I also have some quals that go years out before expiring.

 

[Desu Nota from Columbus]

This can still be done but I'm not sure how to do it directly.

I would still do it using conditional formatting but you would need to inject today's date.

I am not good enough at excel to give you the best way to do it but there is a way to do it with if statements:


If =(today()-A1)>=90 then turn yellow, etc.

There is code for doing this, perhaps search the forum for changing format based on criteria to get the right code.


Sorry i can't help you.

 

[Desu Nota from Columbus]

wow i just had an epic brain fart


in A1 do conditional formatting for each condition (90=90 days after date in a1)

=abs(a1-today)>=90 and set your formatting

copy/paste special/format to entire column


And you can set the three conditions you want. Change the number at the end to reflect however many days after the date you want it to change colors.





This has been a long day, I obviously need to go home now.