• Home
  • Microsoft Courses
  • Blog
  • Newsletter
  • About
  • Contact

The Training Lady

The Training Lady - Tips and Tricks for using Microsoft Office and Windows.

Search this site

  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft PowerPoint
  • Microsoft Word
  • Microsoft Windows
    • Microsoft Windows 7
    • Microsoft Windows 8
    • Microsoft Windows 10

Use conditional formatting to identify dates earlier than today

Published: February 20, 2017 By Belinda Anderson Last Updated: May 31, 2019

Share this:
Print Friendly, PDF & Email

During a Microsoft Excel training session a few week’s ago, I had a great question about using conditional formatting to highlight dates earlier than the current one, in other words, how can we highlight dates that occur in the past? This was a great question and so I thought I would publish a post this week about how to do this.

If you haven’t used conditional formatting in Excel before, be sure to check out my post outlining how to use conditional formatting in Excel.

  1. Open the workbook you wish to apply the formatting to
  2. Highlight the column which contains the dates you wish to work with
  3. From the Home tab select the Conditional Formatting button and choose New Rule
  4. The New Formatting Rule dialog box will appear:

  1. From the Select a Rule Type, choose Format only cells that contain
  2. In the Edit the Rule Description section you need to tell Excel that any cell which contains a date which is less than today’s date, highlight it.

Conditional Format Date

  1. In the Format only cells with section, leave the first drop down menu set as Cell Value
  2. For the next drop down menu change it to less than as shown below:

Conditional Format Date

  1. In the formula box type =TODAY()
  2. This formula will look for a cell value that is less than “today”:

Conditional Format Dates

  1. Now to specify a format you wish to apply, click the Format button
  2. I have chosen to have the text change to Red and Bold

Conditional Format Dates

  1. Click OK
  2. Click OK again to complete the Conditional Formatting rule
  3. Any dates which occur prior to today’s date will now appear in the formatting you selected:

Conditional Format Dates

Note: For the above example, today’s date is 20 February 2017.

Want to conditional format for a range of dates?

Click here to see how you can use conditional formatting for a range of dates.

Love this article? Comment below and let me know how you’ve utilised this feature. Be sure to check out my other Microsoft Excel content here.

Share this:

Filed Under: Microsoft Excel Tagged With: conditional formatting, dates, formatting

Comments

  1. Jenette says

    May 25, 2019 at 8:09 am

    I’ve searched & searched & hope you can help…
    I have a column with various due dates. I want the due date cells to highlight 15 days before the date entered (sort of a warning that the due date is coming up). So if I have a due date of 5/15/2020, i would like the cell to change colors on 5/1/2020, etc. Is there a way to do that?
    Thanks so much!

    Reply
    • Belinda Anderson says

      May 31, 2019 at 12:56 pm

      Hi Jenette,

      This is a great question and one I have had a few times. So I’ve written you a full post with the instructions on how to do this. You can view it at http://www.thetraininglady.com/conditional-formatting-dates-range/. Let me know how you go.
      Thanks, Belinda

      Reply
  2. Maria says

    May 15, 2019 at 2:55 pm

    How can you ignore blank cells with this formula?
    thanks

    Reply
    • Belinda Anderson says

      May 16, 2019 at 3:34 pm

      Hi Maria,

      Because this is achieved using Conditional Formatting, and not an actual formula we are unable to have it ignore blank cells. If you only apply font based formatting such as bold text, or change the font colour, the blank cells will not change in appearance. If however, you apply formatting such as a Fill colour then blank cells will also be formatted. Simple switch the formatting type over to just the options on the Font tab within the Format Cells dialog box and you should have no formatting being applied to blank cells. Hope this helps.

      Belinda

      Reply
  3. Michelle says

    March 7, 2019 at 8:44 am

    I tried several other search results, none of which worked. This is the only one that actually worked and was simple to do – thank you!

    Reply
    • Belinda Anderson says

      March 7, 2019 at 10:29 am

      Thank you Michelle for the feedback. So glad to hear it worked and was simple. 🙂

      Reply
  4. sally says

    November 3, 2018 at 6:45 pm

    Hi this is a great help but I want to add a second condition to clear when the task is completed by another cell having a Y entered in it – how do I do that please? (I don’t even know the terminology to look it up I’m afraid)

    Reply
    • Belinda Anderson says

      November 5, 2018 at 7:29 pm

      Hi Sally,
      From what you have written I think you could use a combination of an IF statement and then conditional formatting to achieve what you are after. If you’d like to send a screenshot of your workbook to info@thetraininglady.com I’m happy to take a look at it and give you some details, or even upload a post to the site with instructions on how to achieve what you are after.
      Thanks,
      Belinda
      aka The Training Lady

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

9 − three =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us, Subscribe or Make Contact

  • Email
  • Facebook
  • Instagram
  • RSS
  • Twitter

Want to receive our newsletter?

Recent Posts

  • How to use conditional formatting on an entire rowHow to conditional format an entire row based on dates older than today
    November 29, 2019
  • Create a visual presentation using Microsoft SwayHow to create a digital presentation using Microsoft Sway
    August 14, 2019
  • Create a quiz using Microsoft FormsHow to create an online quiz using Microsoft Forms
    August 8, 2019
  • 3D Model Animations in PowerPoint 3653D Model Animations in PowerPoint 365
    August 8, 2019
  • Conditional Format to highlight a date rangeUse conditional formatting to identify dates within a specific range
    May 31, 2019
  • BODMAS AcronymExcel giving you the wrong answer? Discover BODMAS and Excel
    May 28, 2019
  • How to add a second time zone to your CalendarHow to display a second time zone in your Outlook Calendar
    May 14, 2019
  • How to change line spacing between bullet and number listsHow to change spacing between items in a list in Word
    April 16, 2019
  • How to Mail Merge bulk emails in WordHow to send bulk emails using Mail Merge in Word
    December 28, 2018
  • How to store a custom list in Excel
    December 14, 2018
  • Create folders to store emails in OutlookCreate folders to store emails in Outlook
    October 23, 2018
  • Receive automatic updates from your favourite websites using RSSHow to subscribe to your favourite websites using RSS
    August 26, 2018
  • Streamline email processes using Quick Parts in OutlookHow to reuse text and images using Quick Parts in Outlook
    August 17, 2018
  • Rehearse timing of your presentationRehearse timings of your PowerPoint presentation
    August 10, 2018
  • The Training Lady Turns 4A Happy 4th Birthday
    July 29, 2018

Want to receive our newsletter?

Join our other Facebook Devoted Fans!

Tweets by traininglady1

Copyright © 2019 · The Training Lady (Trading as Beltan Consultancy) · Privacy Policy