Conditional Format – Dates (Exel 2000-2004)

Topic

New Reply

I am tracking expiration dates on medications. I set up an Expiration flag column using a LeadTime variable and this equation: =IF(ISNUMBER(H22),(IF(H22<TODAY()+LeadTime,"T","")),"") that works fine. Now I have a complication of having eight identical packs with possibility of differing expiration dates. I don't want to add 7 columns for the Expiration flags, so I thought of using conditional formatting. I guess I must be messing up the date arithmetic. I tried to replace the LeadTime variable with 3 conditions using numeric values … ExpDate<60 days, between 60 and 120 and between 120 and 180 days out. This is not quite as flexible as a variable, but would give good visibility if I could color code the date differently for these 3 conditions. I tried both Cell value and Formula, but seem to go in circles with the Cell Value returning only the first condition, regardless of whether it is true or not, and error messages on all the formulas I entered when using Formula is. Any thoughts would be appreciated!
Thanks

Reply | Quote

Replies

Say that your expiration dates are in A1:A100. For a different range, use the first cell of the range instead of A1 in the formulas below.
Select these cells.
Select Format | Conditional Formatting…
Select Formula Is in the first box.
Enter =AND(ISNUMBER(A1),A1>.
Repeat, but now with formula =AND(ISNUMBER(A1),A1>.
Repeat, but now with formula =AND(ISNUMBER(A1),A1<TODAY()+180), and a third kind of formatting.
Finally, click OK.

Reply | Quote

Hans,
This works just fine, I had not put the AND at the front, and I had thought I needed to use between, not recognizing that the FIRST condition met does the formatting, so you saved me a lot of confusion. I also have interspersed, items that have no expiration dates in the columns. That is the reason for the ISNUMBER term. I had formatted the data with expiration dates of “None” being left justified and plain text, those with dates bold and right justified to make it obvious in a quick scan of the data. When I copy the formatting to all cells, I get the RJ and Bold where I don’t want it. So I assume the easiest solution is to just apply the formatting to the cells that have dates … but that is tedious because I have several of these files for different types of packs and boxes of supplies. Since I only have 3 conditions for formatting, is there away to add the RJ/Bold easily for all dates, even those not meeting the special color I have created for various ordering lead times? That is, exclude the RJ/Bold from “None” data fields.
Thank you, again, for your help!

Reply | Quote

You have 4 possible formats to use:
1-3) one for each condition
4) the explicit formatting (when none of the conditions are met)

It seems that you want 5 (the 1-3) the 4) RJ/Bold and the 5)LJ/not bold

If you don’t need it “live” you can select the column, make it all RJ/Bold and then
edit – goto-special
choose constants and text
and remove the bold and lJ the selection.

But if you added a date later it would not automatically change to RJ/Bold.
If you need it live you could add a worksheet_change macro to change the explicit formatting when the cell changed.

If you need help with this let us know…
Steve

Reply | Quote

Thank you Steve,
This will work just fine for now. Since I have to distribute this to others using various versions of Excel (PC and Mac), I think I will stay away from macros for now. (I may tackle it later for my own edification though!) Every time I ask for help I learn something new … I had never really explored the GoTo Special in this detail, it is quite powerful and I will be using it more in the future.
I really appreciate you guys and your expertise!
Thanks again!

Reply | Quote