+ Reply to Thread
Results 1 to 11 of 11

Date Conditional Format question

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Date Conditional Format question

    Hello, I'm trying to create some conditional formats for my project tracker.

    What is trying to do is based on the current date today() in this example. 4/1/2019

    4/5/2019 equal to today's date or greater is green
    3/27/2019 Less than today's date but not greater than 5 days late yellow
    3/26/2019 Less than today's date but greater than 6 days late red

    Any help would be appreciated

    Thanks

    Coady

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Date Conditional Format question

    Hello Coady & Welcome to the Forum,

    Conditional Formatting
    • Highlight applicable range >> A2:A200
    • Home Tab >> Styles >> Conditional Formatting >> New Rule
    • Select a Rule Type: Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =A2>=TODAY()
    • Format… [Number, Font, Border, Fill] >> Green
    • OK >> OK

    For Red =AND(A2 < TODAY(),A2+5 < TODAY())
    For Yellow =AND(A2 < TODAY(),A2+4 < TODAY())

    http://www.xldynamic.com/source/xld.CF.html#due
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Date Conditional Format question

    Thanks for the help Jeff,

    The green and red are working, but the yellow is not.

    If I use 3/27/19 it works, but if I use 3/28/2019 - 3/31/19 there is no formatting.

    Thanks,

    CK

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Date Conditional Format question

    CK,

    Can you attach a sample file with what you have so we don't have to make a full test file?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Date Conditional Format question

    I changed what I wanted to accomplish

    I have attached the worksheet.

    If the date is less than today's date it's red - row 3 should be red
    If the date is within 5 days of today's date then yellow - row 4 would be yellow
    if the date is more than 6 days away its green - row 5 - 19 should be green
    is there is no date in E there is not color in I. rows 20 - 24
    The column being looked at is E. The conditional format is in I

    Thanks for the help

    Coady
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Date Conditional Format question

    If the date is less than today's date it's red - row 3 should be red
    I'm loosing you here. In row 3, the date is 5-Apr which is NOT less than today's date. How would that meet the definition of red?

  7. #7
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Date Conditional Format question

    Row 3 is 3/31/19 are you looking at the ID# if so it's ID 1... sorry I know that's confusing

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Date Conditional Format question

    Sorry, but on the file you posted, row 3 ID#1 is 5-Apr in cell E3

  9. #9
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Date Conditional Format question

    Sorry maybe I didn't hit save before I uploaded. Sorry for the confusion.

    I have re uploaded the file
    Attached Files Attached Files

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Date Conditional Format question

    Try this...

    Red =AND(TODAY()-E3>0,E3<>"")
    Yellow =AND(TODAY()-E3>=-4,E3<>"")
    Green =E3>=TODAY()

    in that order

  11. #11
    Registered User
    Join Date
    10-16-2012
    Location
    St. Louios
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Date Conditional Format question

    That worked, thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Format Question
    By theTaoJones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2018, 09:22 AM
  2. [SOLVED] Conditional format question
    By Kymba in forum Excel General
    Replies: 2
    Last Post: 03-30-2016, 03:11 AM
  3. [SOLVED] conditional format question
    By mark a c in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-16-2013, 11:37 AM
  4. [SOLVED] Question regarding conditional format question that covers two range criteria
    By lilsnoop in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2013, 12:32 PM
  5. Replies: 1
    Last Post: 01-26-2006, 10:10 PM
  6. [SOLVED] Conditional format question
    By [email protected] in forum Excel General
    Replies: 7
    Last Post: 09-16-2005, 12:05 PM
  7. [SOLVED] Date Format Question- convert into a usable date format.
    By Josh O. in forum Excel General
    Replies: 1
    Last Post: 02-10-2005, 06:06 PM

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