Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Do Conditional Formatting of Date

We can format the date of an event or expiration by setting a reminder and using colours to identify dates that exceeds or fall within the range of our chosen dates. This tutorial would teach us how to format dates conditionally using two different methods.

Figure 1: Conditional Date formatting

Data to Conditionally Format based on date

We will prepare a table of dates. We will be working on expiration dates for Method A.

Figure 2: table of dates

We will now highlight the expiration date column, select conditional formatting on the task bar and select highlight cell rules

Figure 3 – Select Conditional Formatting

  • We will select less than and input this formula into the dialogue box “=today()”. This will condition the date in Cell A4 (pink)

Figure 4: First Conditioning

 

  • We will now do our second conditioning still on the column of expiration dates, by selecting conditional formatting on the task bar, select highlight cell rules, then we will select between. We will now type into the first dialogue box =today(), and in the second =today()+30. Then we will select the colour to fill Cell A5

 

Figure 5: Second Conditional formatting

 

  • Finally, we will do our third conditional formatting for dates exceeding the expiration date, by selecting conditional formatting on the task bar, select highlight cell rules, then we will select greater than. We will now type into the dialogue box =today()+30. Then we will select the colour to fill Cell A6 with a third fill.

 

Figure 6: Third Conditioning

The interpretation of the date format is that the yellow icon signifies any date between today and 30 days, while the red icon signifies any date less than today

Method B

  • We can use the icon set to also format date conditionally by providing a set of dates and follow the steps below

Prepare dates

  • We will prepare dates for the second column of due dates in order to apply the second method.

Figure 7: Date preparation

  • We will now highlight the dates, select conditional formatting, select the icon sets and click on the first set in the shape section.

Figure 8: Selection of Icon sets

 

  • We will now select conditional formatting again, click on manage rules, select the icon sets and click on edit rule

 

Figure 9: Editing Icon set rules

  • We will change the parameters of Type to Formula, and then in the Value section, we will type the formula =today()+30 into the upper dialogue box, and we will type =today() into the lower dialogue box. We will click OK

Figure 10: Edited parameters of icon sets

The interpretation of the date format is that the yellow icon signifies any date between today and 30 days, while the red icon signifies any date older than today

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc