+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting formulas for dates

  1. 10-31-2007, 10:25 AM #1
    oakman
    oakman is offline
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117

    Conditional formatting formulas for dates

    Greetings to all.
    I am trying to understand how formulas work when Conditional formatting. I have a date in cell E1(This date is not today's date). I also have a range of other dates in M4:M17. I would like to make whatever date is in cell E1 to be thought of as the current date. I would then like to formate any date in M4:M17 that is the same as the date in E1 to be Yellow. In addition I would aslo like to format any date in M4:M17 that is any time behind the date in cell E1 - this I would like to do in Red. Any help in accomplishing this would really be appreciated.
    Reply With QuoteRegister To Reply
  2. 10-31-2007, 10:30 AM #2
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    Quote Originally Posted by oakman
    Greetings to all.
    I am trying to understand how formulas work when Conditional formatting. I have a date in cell E1(This date is not today's date). I also have a range of other dates in M4:M17. I would like to make whatever date is in cell E1 to be thought of as the current date. I would then like to formate any date in M4:M17 that is the same as the date in E1 to be Yellow. In addition I would aslo like to format any date in M4:M17 that is any time behind the date in cell E1 - this I would like to do in Red. Any help in accomplishing this would really be appreciated.
    1. Select the range M4:M17 with M4 as the active cell
    2. Select Format Conditional Formatting
    3. in Condition 1 "Cell Value is" in first dropdown and "=$E$1" in the formula box without the quote marks
    4. select the formatting to pattern = yellow
    5. in Condition 2 "Cell Value is" in first dropdown and "<$E$1" in the formula box without the quote marks
    6. select the formatting to pattern = red
    ---------------------------------------------------
    ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
    To insert code into the VBE (Visual Basic Editor)
    1. Copy the code.
    2. Open workbook to paste code into.
    3. Right click any worksheet tab, select View Code
    4. VBE (Visual Basic Editor) opens to that sheets object
    5. You may change to another sheets object or the This Workbook object by double clicking it in the Project window
    6. In the blank space below the word "General" paste the copied code.
    Reply With QuoteRegister To Reply
  3. 10-31-2007, 10:57 AM #3
    oakman
    oakman is offline
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Thank you for the quick response. However, I have a question. When I choose "Cell Value is", I have to choose another option next to it. The choices range from "equal to" to "less than or equal to". Which of these should I pick?
    Reply With QuoteRegister To Reply
  4. 10-31-2007, 01:09 PM #4
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    Quote Originally Posted by oakman
    Thank you for the quick response. However, I have a question. When I choose "Cell Value is", I have to choose another option next to it. The choices range from "equal to" to "less than or equal to". Which of these should I pick?
    For Condition 1 select = and leave the formula "$E$1" withouth the quotes
    For Condition 2 select < and leave the formula "$E$1" withouth the quotes
    Reply With QuoteRegister To Reply
  5. 10-31-2007, 02:09 PM #5
    oakman
    oakman is offline
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Thanks again for the respose, but what I meant when I quoted "equal to" and "less than or equal to" was that these where actual text choices that Excel presents to me in a drop down menu when I choose "Cell Value is". I can see how you thought I meant not knowing which sign to use for the formula. Try pulling up the Conditional Formatting box and my problem will make sense. Thanks so much again for your future assistance!
    Reply With QuoteRegister To Reply
  6. 10-31-2007, 02:43 PM #6
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    ok one more time for clarity

    For Condition 1 Cell Value Is and select "Equal to" from drop down and leave the formula "$E$1" withouth the quotes
    For Condition 2 Cell Value Is and select "Less Than" and leave the formula "$E$1" withouth the quotes
    Reply With QuoteRegister To Reply
  7. 10-31-2007, 03:13 PM #7
    oakman
    oakman is offline
    Forum Contributor
    Join Date
    05-06-2004
    Posts
    117
    Perfect. Thank you very much for your help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1