Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Format - Dates (Exel 2000-2004)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format - Dates (Exel 2000-2004)

    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<TODAY()+60) in the box next to it.
    Click Format... and specify the format you would like to apply.
    Click OK to return to the Conditional Formatting dialog.
    Click Add >>.
    Repeat, but now with formula =AND(ISNUMBER(A1),A1<TODAY()+120), and different formatting.
    Click Add >>.
    Repeat, but now with formula =AND(ISNUMBER(A1),A1<TODAY()+180), and a third kind of formatting.
    Finally, click OK.

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format - Dates (Exel 2000-2004)

    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!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Format - Dates (Exel 2000-2004)

    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

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format - Dates (Exel 2000-2004)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •