+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting of Dates

  1. #1
    Registered User
    Join Date
    09-29-2022
    Location
    West Midlands, UK
    MS-Off Ver
    365
    Posts
    3

    Lightbulb Conditional Formatting of Dates

    Hi Everyone,

    I am trying to conditionally format a column of dates where the cell changes colour dependent on how much time is pending or elapsed from todays date. I have tried to attach a picture but if that hasn't worked I need:

    DATE WITHIN 3 MONTHS OF TODAY AMBER
    THE DATE PASSED UP TO 3 MONTHS AGO RED
    DATE PASSED OVER 3 MONTHS AGO DARK RED
    DATE IS OVER 3 MONTHS IN FUTURE GREEN

    excel pic.jpg

    I hope that makes sense,

    Thank you,

    Rosie

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting of Dates

    Highlight C2:C13 > Conditional Formatting > New Rule > Use a formula
    =AND(A$2>=EDATE(C2,-3),A$2<=C2)
    Format: Fill amber

    Highlight C2:C13 > Conditional Formatting > New Rule > Use a formula
    =AND(A$2<=EDATE(C2,3),A$2>C2)
    Format: Fill red

    Highlight C2:C13 > Conditional Formatting > New Rule > Use a formula
    =A$2>EDATE(C2,3)
    Format: Fill dark red

    Highlight C2:C13 > Conditional Formatting > New Rule > Use a formula
    =A$2<EDATE(C2,-3)
    Format: Fill green
    Last edited by 63falcondude; 09-29-2022 at 09:02 AM.

  3. #3
    Registered User
    Join Date
    09-29-2022
    Location
    West Midlands, UK
    MS-Off Ver
    365
    Posts
    3

    Re: Conditional Formatting of Dates

    I cannot begin to tell you how much I appreciate you! Thank you!!

  4. #4
    Registered User
    Join Date
    09-29-2022
    Location
    West Midlands, UK
    MS-Off Ver
    365
    Posts
    3

    Question Re: Conditional Formatting of Dates

    The light red is not working and its just appearing as amber when it should show light red?

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Conditional Formatting of Dates

    I have just answered a similar case in the link below.

    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  6. #6
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Conditional Formatting of Dates

    Hi,
    I tried to sketch a solution. I hope it is what you need. The formulas for the conditional format are also written in the cells related to the names of the colors.
    Attached Files Attached Files
    Diana Tanase


    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting of Dates

    Quote Originally Posted by RosieT81 View Post
    The light red is not working and its just appearing as amber when it should show light red?
    It works fine on my end. Make sure that the first row that you highlight is the same as the row used in the formula.

    Highlight C2:C13 > Conditional Formatting > New Rule > Use a formula
    =AND(A$2<=EDATE(C2,3),A$2>C2)
    Format: Fill red

    If you are still having issues, consider attaching a small sample sheet here that shows the formulas applied, and I will see what is going on.

+ 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: 5
    Last Post: 12-03-2020, 01:45 PM
  2. Conditional formatting using dates and using dates without years
    By dcef79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 05:35 AM
  3. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  4. [SOLVED] Conditional Formatting Due dates and Completed Dates
    By shansen79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-30-2014, 01:33 PM
  5. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  6. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  7. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 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