+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting - Dates before this MONTH

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Conditional Formatting - Dates before this MONTH

    Hello!

    I can easily format a cell if the date in it is before TODAY, but I only want to format cells where the date is before today's MONTH... for example, if the date in the cell is 1st June and today is 22nd June, I do NOT want it highlighted. If its 31st May ro earlier, then I DO want it highlighted.

    I'm sure its super easy, but I just cant think of it right now and its driving me nuts!!

    Many thanks

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Conditional Formatting - Dates before this MONTH

    Hi Lesley,
    In the sample below, I am formatting cell G5:
    =MONTH(TODAY())>MONTH($G$5)
    ..basically the month in which we are today needs to be greater than the month of the date in G5.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  3. #3
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Conditional Formatting - Dates before this MONTH

    Hi ORoos
    That so ALMOST perfectly works! What I omitted ws that it needs to include the year... so June 2020 would NOT be highlighted, but June, July, Aug etc in 2019 would. Your formula seems to work on month only. Can you help to refine it please?

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Conditional Formatting - Dates before this MONTH

    Try tihs:

    =G5<=EOMONTH(TODAY(),-1)

  5. #5
    Registered User
    Join Date
    10-31-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Conditional Formatting - Dates before this MONTH

    Yay! That's perfect! Thank you so much for your help

+ 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 formatting based on Current month & month of another cell.
    By KayA. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-14-2018, 03:25 PM
  2. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  3. [SOLVED] Copy Conditional Formatting Month to Month
    By kitttieluv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2016, 07:39 PM
  4. [SOLVED] Conditional formatting based on dates (month and year specifically)
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-07-2015, 06:41 PM
  5. [SOLVED] Conditional Formatting based on calculated future dates (month only, ignore day and year)
    By Nitsirk82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 06:42 PM
  6. [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
  7. Replies: 8
    Last Post: 12-22-2009, 03:15 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