+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting based on dates

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Conditional Formatting based on dates

    I wanted to highlight an entire row based on the date in a column. Yellow if it's equal to today, Red if it's past 4 days from today, and Green if it's sooner than today.
    I used these formulas and they worked:

    =TODAY()=$M2 (Yellow)
    =TODAY()+4>=$M2 (Red)
    =TODAY()<=$M2 (Green)

    Only problem is that I want the rows to remain blank if there is nothing in the M column and they all appear red. The column has a formula K2+L2 (Where column K is the date where an invoice is sent and column L are the net terms) and the default date that appears is 1/0/00. All those rows are red and I need them blank, until data is entered into columns K and L. Hope that makes sense.

  2. #2
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Conditional Formatting based on dates

    I think the default date is 01/01/1900 you could always set conditional formatting based on that specific date.

    = 01/01/1900 (No formatting)

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional Formatting based on dates

    I entered that as a condition and it brought back an error. Do I need to enter the formula a particular way?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Conditional Formatting based on dates

    You can check if M is empty/not empty.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    01-24-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional Formatting based on dates

    I tried changing the formula to check for empty cells and it worked as long as I erased what was in the cell, but since I have a simple sum formula (K2+L2) in that column it remains red. I can always drag the formula down as I go, but would rather find a fix around that. Can I add a new condition to leave the row blank if all that's in there is the formula, without it converting to an actual date once I populate columns K and L?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Conditional Formatting based on dates

    What's the SUM formula in the cell?

    Can you attach a sample workbook?

  7. #7
    Registered User
    Join Date
    01-24-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Smile Re: Conditional Formatting based on dates

    I have attached the file for your review. Thank you so much for all your help.
    Attached Files Attached Files

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Conditional Formatting based on dates

    You can just change the "" to 0 in the formula.

  9. #9
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Conditional Formatting based on dates

    Create a new rule and the code is
    =$M2=0
    and make sure the background is White (not blank)
    make the rule the top most rule

  10. #10
    Registered User
    Join Date
    01-24-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional Formatting based on dates

    Beautiful! That did it. Thank you so much for all your assistance and patience with my issue.

+ 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