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