+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting - Cell colour based on date

  1. #1
    Registered User
    Join Date
    05-02-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    4

    Conditional Formatting - Cell colour based on date

    I'm looking for the correct formulas for the following:

    Cell contains a date. Cell must be: colour red up to date entered in cell, colour yellow from date +7 days, colour green from +8 days onwards from date entered in cell.

    I understand it involved rules for conditional formatting but the formulas used don't seem to be working.

    Thanks very much.

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Conditional Formatting - Cell colour based on date

    Attach a sample workbook. 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.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    05-02-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    4

    Re: Conditional Formatting - Cell colour based on date

    I'm afraid the website won't let me attach the example to the message (the dropdown menu won't open properly), though I was able to upload it in Manage Attachments.

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Conditional Formatting - Cell colour based on date

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window. there is not any drop down!!!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    Re: Conditional Formatting - Cell colour based on date

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =A1<=TODAY()...Format Fill RED

    Repeat 3 and 4 with...
    =A1<=TODAY()+7...Format Fill Yellow

    Repeat 3 and 4 with
    =A1>today()+7 Format Fill Green
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    05-02-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    4

    Re: Conditional Formatting - Cell colour based on date

    Mr. Dibbins, thanks very much, that's a massive help. I'll have a play with it because it's not quite exactly what I need yet, but is there any way to alter the formula so it's not from the current date but a specific date entered into the cell?

    The purpose of the cell is to be able to enter a date into the cell for the start of a course and have the cell be red up until that date, yellow while the course is on (7 days) and green once complete.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    Re: Conditional Formatting - Cell colour based on date

    Quote Originally Posted by HiYoJoe View Post
    Mr. Dibbins, thanks very much, that's a massive help. I'll have a play with it because it's not quite exactly what I need yet, but is there any way to alter the formula so it's not from the current date but a specific date entered into the cell?

    The purpose of the cell is to be able to enter a date into the cell for the start of a course and have the cell be red up until that date, yellow while the course is on (7 days) and green once complete.
    Yes, sure, all you need to to is replace TODAY() with your cell reference containing the date you will use....
    4. enter =A1<=$Z$1...Format Fill RED

    Repeat 3 and 4 with...
    =A1<=$Z$1+7...Format Fill Yellow

    Repeat 3 and 4 with
    =A1>$Z$1+7 Format Fill Green

    where Z1 contains your date. It could, of course, be any cell you need it to be, doesnt have to be Z1

    And thanks for the rep

  8. #8
    Registered User
    Join Date
    05-02-2017
    Location
    London
    MS-Off Ver
    2007
    Posts
    4

    Re: Conditional Formatting - Cell colour based on date

    Mr. Dibbins,

    Again thanks for taking the time to help. Unfortunately it still isn't doing what it's being asked to do. The format of the cell is Date. Can I confirm whether A1 in the formula should always be A1 or the cell being affected? Also with the formula i'm using (=$J$26>$J$26+8) I understand that it should affect cell J26 and format the cell when the value of J26 (the date of the course) exceeds eight days. But the cell does not turn green as it should. How does the order of the rules in the Manage Rules window affect it?

    SampleFormulas.jpg
    Attached Images Attached Images
    Last edited by HiYoJoe; 05-08-2017 at 05:06 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Conditional Formatting - Cell colour based on date

    =$J$26>$J$26+8

    does not make sense as you are comparing a cell with itself.

    Perhaps

    =TODAY()>$J$26+8

    Re-read Ford's (Mr Dibbins) reply #7

+ 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. Replies: 6
    Last Post: 04-11-2016, 09:48 AM
  2. Replies: 2
    Last Post: 05-12-2015, 04:39 PM
  3. [SOLVED] Partial conditional formatting based on the colour of a cell
    By ello2001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-07-2014, 08:17 AM
  4. [SOLVED] Conditional Formatting to Change Cell Colour Based on Date
    By Kym2101 in forum Excel General
    Replies: 5
    Last Post: 05-01-2012, 11:41 PM
  5. Conditional Formatting - Set colour based on date past
    By jdm3098 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2011, 10:41 AM
  6. conditional formatting based on cell colour
    By nervous_pilchard in forum Excel General
    Replies: 0
    Last Post: 07-12-2011, 05:29 AM
  7. Conditional formatting by date-need to colour a cell
    By duckboy1981 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2009, 06:35 AM

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