+ Reply to Thread
Results 1 to 13 of 13

Conditional Formatting of Cells based off of date and time criteria

  1. #1
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Conditional Formatting of Cells based off of date and time criteria

    Solved: See post number 7 in this thread.

    Hello,

    Here is what I am trying to do.

    I have three cells right now:

    Date of incident
    Due Date
    Date completed

    What I would like to do is the following:

    When they enter the date of the incident. There are specific time frames based on the incident. I have each incident type on their own tab.

    So, in this particular instance, you have 30 days to complete the item.

    Date of incident: 1/1/2015 (A column)
    Due Date: 1/30/15 (B column)
    Date completed: 1/20/15 (D column)

    What I would like to do, if possible. Is when they add the date of incident in say cell A3, cell B3 would automatically add the 30 days. Which I have working using the:=$A3+30 That part works. I do not need to worry about weekends or holidays since those are counted in this process.

    What I would like to do is have it so when you enter the date of the incident into the A3 box or any of those cells in the "A" column, the 30 days is added but the cell in the "B" column turns say green. After 15 days the same cell turns yellow, then 3 days to do date it turns red.

    However once the date is entered under Date Completed, all of the coloring goes away. At this point it doesn't matter if the item was late or not. So I would like to remove all of the coloring.

    I also have a cell A1 that is putting in the current date when you open the file.

    So is this possible within excel?

    Thanks for any and all advice in advance.
    Last edited by rhett7660; 12-16-2015 at 05:38 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Can this be done in Excel?

    See Rule 1

    http://www.excelforum.com/forum-rule...rum-rules.html

    before a moderator comes along
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,071

    Re: Can this be done in Excel?

    Yes, it is possible, using Conditional Formatting.

    Deleted rest of post - title has been changed.

    Pete
    Last edited by Pete_UK; 12-16-2015 at 02:17 PM.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,512

    Re: Can this be done in Excel?

    Waiting for new title
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Can this be done in Excel?

    didn't see moderator comments before posting...

  6. #6
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Conditional Formatting of Cells based off of date and time criteria

    Title changed... sorry about that.....

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting of Cells based off of date and time criteria

    In conditional formatting, for cell B3
    Choose "Use a formula to determine..."

    =AND(A3<>"",TODAY()>=A3+15,D3="")
    Choose a Yellow format

    =AND(A3<>"",TODAY()>=C3-3,D3="")
    Choose a Red format

    =AND(A3<>"",D3="")
    Choose a Green format
    Last edited by Jonmo1; 12-16-2015 at 01:56 PM.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting of Cells based off of date and time criteria

    The one for Red, the C3 should be B3...

    And they should be in order in the Conditional Formatting Manager window, from Top to Bottom Red Yellow then Green.

  9. #9
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Conditional Formatting of Cells based off of date and time criteria

    @Jonmo1

    Thank you very much. Let me work this out and get back to you!

  10. #10
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Conditional Formatting of Cells based off of date and time criteria

    Quote Originally Posted by Jonmo1 View Post
    The one for Red, the C3 should be B3...

    And they should be in order in the Conditional Formatting Manager window, from Top to Bottom Red Yellow then Green.
    Thank you very very much! This worked like a charm!!!!

    One last question. How do I make sure these conditional formatting rules apply to my columns. Since I don't know how many entries there will be, do I need to do something different in order to apply these to the entire column?
    Last edited by rhett7660; 12-16-2015 at 02:46 PM.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,512

    Re: Conditional Formatting of Cells based off of date and time criteria

    If you go to "Manage" conditional Formatting Rules, you''ll see the range that each is set for (all should be the same # of rows). Change that to 10 % more than the largest amount of entries you expect to have.

  12. #12
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Conditional Formatting of Cells based off of date and time criteria

    Quote Originally Posted by ChemistB View Post
    If you go to "Manage" conditional Formatting Rules, you''ll see the range that each is set for (all should be the same # of rows). Change that to 10 % more than the largest amount of entries you expect to have.
    Perfect!! Thank you very much! I appreciate everyone's help on this!!!!

  13. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Conditional Formatting of Cells based off of date and time criteria

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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