Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Conditional Formatting (Table) Based on Dates

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting (Table) Based on Dates

    Okay, so I've been trying to find an answer to this particular problem for a couple days now and no luck. There are instructions on conditional formatting applied to table, but I can't seem to tweak them so that they work for my specific situation.

    Hopefully somebody here can help

    I have a table that is meant to track the intake of new clients, and I'd like to have the text colour change based on one of two date entries. If there is an entry under "Closed" I want the text (of the entire row) to grey, whereas if the "Opened" date is more than 4 months ago I want the text to turn red.

    I haven't even started trying to get that second bit working as I can't even get the text to grey.

    (Here's a pic of the table I'm working with)


    Please let me know if you need me to clarify what/how I'm using the table.

    Thank you in advance!

  2. #2
    Board Regular mhillmann's Avatar
    Join Date
    Mar 2015
    Location
    Illinois, USA
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    Got it. Use these as formulas in the conditional formatting panel.

    Turn this one red:
    Code:
    =DATEVALUE(TEXT($D3, "mm/dd/yyyy")) + 30 < DATEVALUE(TEXT(TODAY(), "mm/dd/yyyy"))
    Turn this one grey:
    Code:
    =NOT(ISBLANK($K3))
    Then make sure you have it apply to all the cells in the table, not just rows D and K.

  3. #3
    New Member
    Join Date
    Dec 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    Sorry about the double post, but I thought I might make a mock-up of what I (visually) want the table to look like when working. Now, I added extra shades of text based on just how far back the "Opened" date was, but I'm not sure if I'll actually need that for the table. (And that's a pretty easy tweak once we've figured out the base formula needed)



    If it matters -the data is either going to be kept ordered based on the date "Opened" or alphabetically by name. (So ideally, some sort of formula that doesn't break if I switch between those two sorting options)

  4. #4
    Board Regular mhillmann's Avatar
    Join Date
    Mar 2015
    Location
    Illinois, USA
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    I forgot to mention a few things. The + 30 means 30 days between today and the date shown. You can change that if you wish. Also you need to order them correctly, the one that turns grey being higher on the list, so that the red ones turn grey once a finished date is added.

  5. #5
    New Member
    Join Date
    Dec 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    Does it matter that my date format is set up as "mmm dd/yy" under style/format?
    Last edited by psuedodragon; Mar 31st, 2015 at 06:07 PM.

  6. #6
    Board Regular mhillmann's Avatar
    Join Date
    Mar 2015
    Location
    Illinois, USA
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    If you are using a custom format, then no. If not, there may be problems. Let me know how it goes.

  7. #7
    New Member
    Join Date
    Dec 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    :S The grey formula works great, but I can't get the other one to change colour. I'm using a test date "Opened" almost a year ago and it's still stubbornly black.

    Originally I'd just typed it into the formula box (so I could adjust it from 30 to 120 days) and when it didn't work, I actually just copy and pasted the formula you shared above. Still no change.

    I feel like I'm missing something really small and obvious that's messing this up >.<

  8. #8
    Board Regular mhillmann's Avatar
    Join Date
    Mar 2015
    Location
    Illinois, USA
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    It is because the formula works under the assumption that excel knows which date you are talking about, but the format you are using is not recognized... I wrote this, it is long but it should work for you.

    Code:
    =DATEVALUE(DATE("20"&RIGHT(D5, 2), IF(LEFT(D5,3) = "Jan", 1, IF(LEFT(D5,3) = "Feb", 2, IF(LEFT(D5,3) = "Mar", 3, IF(LEFT(D5,3) = "Apr", 4, IF(LEFT(D5,3) = "May", 5, IF(LEFT(D5,3) = "Jun", 6, IF(LEFT(D5,3) = "Jul", 7, IF(LEFT(D5,3) = "Aug", 8, IF(LEFT(D5,3) = "Sep", 9, IF(LEFT(D5,3) = "Oct", 10, IF(LEFT(D5,3) = "Nov", 11, 12))))))))))), RIGHT(LEFT(D5, 6), 2))) + 30 < DATEVALUE(TEXT(TODAY(), "mm/dd/yyyy"))

  9. #9
    New Member
    Join Date
    Dec 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    First of all, I really really (really) appreciate all your help!

    But it still isn't working. :S Might have to take another run at it tomorrow (as my work day is almost done, and this is giving me a headache so I won't work on it at home).


    Thinking about it, Excel should be able to recognize that it's a date. I enter the date as 05/04/2014 (for example) and I just have it set up so that it shows up in the cell as Apr 05/14... but I could be wrong.

  10. #10
    Board Regular mhillmann's Avatar
    Join Date
    Mar 2015
    Location
    Illinois, USA
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting (Table) Based on Dates

    You are actually not wrong. That is possible to do with Excel, it just won't let you do it in reverse. It has to do with the way dates are actually stored in the program. Starting with 1/1/1900 they are stored as an integer in number of days.

    Without being too wordy: You're right but excel doesn't like that.

    The best way to go from here would be to format the cells so that they read as a normal date would (mm/dd/yyyy) or some version of that.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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