+ Reply to Thread
Results 1 to 4 of 4

multiple criteria conditional format with date proximity

  1. #1
    Registered User
    Join Date
    05-06-2005
    Posts
    30

    multiple criteria conditional format with date proximity

    Hi, I'm trying to conditionally format rows in a spreadsheet so that I can determine which rows contain information that needs to be followed up on.

    I'm using this formula in the conditional formatting section

    =AND(C2=TODAY()+1,D2="C",J2="")

    What I need to happen is that anything older than 24 hrs, where D2 is selected as C and where field J2 is left blank should turn yellow to indicate follow up work.

    D2 and J2 are drop down validation lists.

    it's not working though, and I don't know why.


    Thanks for your help

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by B. Baumgartner
    =AND(C2=TODAY()+1,D2="C",J2="")

    What I need to happen is that anything older than 24 hrs,......
    For your formatting to be triggered using that formula C2 would need to be tomorrow's date. If you want the formatting triggered for dates earlier than yesterday then try

    =AND(C2<TODAY()-1,C2<>"",D2="C",J2="")

  3. #3
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hmmm ... your formula is working for me. I made only one change; namely, I replaced C2 with $C2 (similar for D2 and J2). I applied the same formula to both C2 and D2 and both change color together as I make different selections from the validation list for cell D2. Copied the formatting and validation down a few rows ... they all work.

    In the Conditional Formatting dialog, did you change the selection from "Cell Value is" to "Formula Is"?

    ---------- ADDED LATER ---------
    Just saw dll's post. I only tried dates of tomorrow and later and with no time values, only whole number dates. So, the equal sign and the +1 were probably the problem and not the selection.
    Last edited by MSP77079; 01-13-2007 at 08:23 PM.

  4. #4
    Registered User
    Join Date
    05-06-2005
    Posts
    30

    Thanks

    the minus sign was the key. Oh and the =Today as I wanted anything older, not just those equal to.

    Thanks for the quick reply!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1