+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting Dates

  1. #1
    Registered User
    Join Date
    08-20-2020
    Location
    Chicago, IL
    MS-Off Ver
    Excel for Mac 2016
    Posts
    3

    Conditional Formatting Dates

    I have a spreadsheet where I have to keep track of two sets of dates: Date Reviewed (column A) and Date of Contact (column F).

    I need to have the dates in Column F highlight if the date is more than 30 days from the date in column A. The "Today" function doesn't work for me and I have been searching everywhere to figure out how to conditionally format Column F only if the date is more than 30 days from Column A.

    This is my first time posting and I apologize if I missed a thread where this was covered.

    Thank you!

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,894

    Re: Conditional Formatting Dates

    Seeing your data would gave helped ( see yellow banner)
    Perhaps try =$F1 >$a1+30 after selecting the range in col F ?

  3. #3
    Registered User
    Join Date
    08-20-2020
    Location
    Chicago, IL
    MS-Off Ver
    Excel for Mac 2016
    Posts
    3

    Re: Conditional Formatting Dates

    Ok, here is the shell of the spreadsheet I am using.

    I need the date in column F to turn red if they are more than 30 days past from the date in Column A.

    So Line 2 should be red, but line 3 should not.

    Thank you!
    Attached Files Attached Files

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,894

    Re: Conditional Formatting Dates

    Your CF is correct.
    F2 does not change color as the date it contains (15/05/20) is not 30 days past 15/08 (A2)

  5. #5
    Registered User
    Join Date
    08-20-2020
    Location
    Chicago, IL
    MS-Off Ver
    Excel for Mac 2016
    Posts
    3

    Re: Conditional Formatting Dates

    May 15, 2020 is more than 30 days from August 15, 2020, so it should be turning red. The cells that should not turn red are the ones that are between July 15th and August 15th. Any thing before that needs to show red.

    How do I make that happen?

    Thank you so much for your time and help!

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,894

    Re: Conditional Formatting Dates

    Try the following CF =AND($F2<>"";$A2-$F2>30)

+ 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] Multi-Conditional Conditional Formatting and Dates
    By Mousiefuzz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2015, 04:09 AM
  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

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