Conditional Formatting for Dates in Excel (Table of Contents)
Conditional Formatting for Dates in Excel
Conditional formatting for dates in excel means it formats the particular data as per criteria set by you. Here we will talk about formatting dates, so we can highlight / format the dates we want in a given particular format. The selected dates i.e. the dates before today, dates of the Sunday, weekends and many more.
Here in excel, we can select the rules which are already there or we can make our own rule and make our desired condition to find / format the desired data. Let’s see the path to get into conditional formatting.
Step: 1
- First of all, we need to select the data which we need to be formatted.
Step: 2
- Click Home from the Menu Bar and click the Conditional Formatting as per below screenshot.
- From the above screenshot, we can see the options available for the conditional formatting by clicking the conditional formatting from the toolbar.
- Now the options available for us are many but here we have to use to formatting the dates.
Step: 3
- From the screenshot, we can see that there is a sub-option available of A Date Occurring under the category of highlighting cell rules, by clicking the same below box will open to show you some options to highlight the particular dates.
- Here we have an option available to highlight the Date from yesterday to next month, and we also have the built-in option of how we want these dates to be highlighted with. Like the color of text and the colored cell, there are some fixed options. Kindly see the following image for the reference of the options available.
- As you can see there is also an option of custom format, what more, we need. So we can format our required date in our required color and much more, then just click OK and we shall have our formatted dates.
How to Use Conditional Formatting for Dates in Excel?
As we have seen in the above examples the conditional formatting for dates can be used for in situations like Found duplicate dates, highlighting the required dates of the week like by making a formula we can highlight the dates which are in Monday, Sunday or any other day of the week.
By using built-in options we can highlight the dates of Yesterday, Today to Next Month or if the built-in option is not usable for us we can make our own rules and can format the data as we want by using formulas like a weekday, today, date, Now and many others.
Example #1 – Highlight the Dates of Next Week
For this example, we will see the basic example of just highlighting the dates from the last week.
Suppose we have data of drug with its expiry dates as follows.
- Now we are going to select all dates and go to the conditional formatting tool from the Home menu and select the A Date Occurring.
- Here as we want the dates of the next week, we have selected the “Next week” option and for formatting, we have selected the “Light Red Fill with Dark Red text”
- As we can see from below screenshot there are two drugs which have the expiry date of next week which is highlighted with Light Red fill with Dark Red text as required.
Similarly, we can do the same to highlight the dates of Yesterday to Next Month from built-in options.
Example #2 – Highlight Dates of Weekends
For this example, we need to explore the making new rules as there is no built-in option available to highlight the dates of the weekend. Suppose we have a data of drug with its expiry dates as follows.
- Here as there is no built-in option available to highlight the weekends. So, we need to make a rule which identifies the weekend from the given dates and then formatting and highlighting the weekends.
- So first of all, here we need the formula to identify the dates weekends from the given dates, we can use the formula =weekday
- The logic we can use with this formula is =weekday(serial_number, [return_type])
- Here we have serial number C2 to C9, return value will be in column # 2,
- The logic for this formula is we highlight the date for which the weekday is greater than 5.
- So the formula is =weekday(C2, 2)>5
- The output is given below.
- Now repeat the steps, select the date cells C2 to C9 and now click on Conditional Formatting.
- Click on New Rule.
- Select the option Use a formula to determine which cell to format
- Now enter our formula =weekday(C2, 2)>5
- Then select the format by clicking the format button.
- A box “Format Cells” will open and we can fill any color we want.
- Here we have taken Green color, by clicking ok, we will have our weekend dates available in Green filling as per the below image.
- As per the results, we can say three expiry dates are in weekends.
Example #3 – Highlight the Dates Past Today
Now, suppose we have the data for this example as per the below image
- Now here we want to highlight the dates which are just past today (Date: 09/04/2019), so which dates are before 09th April 2019 will be highlighted.
- So for this operation, we do not have any built-in option available.
- Here we can do this operation by using =NOW() function
The steps will be as follows:
- Select the Expiry Dates from column.
- Click on the Conditional Formatting and click on New Rule.
- A box will open, in that select the Rule type Format only Cells that contain.
- In rule description select Cell Value in the first box and Less than in the second box, and type =NOW() function in the third box.
- Now click on format and select the color format according to choice and we’re done
- Here, for example, we have used Green color.
- And click on Ok.
As per the below image, we can see that the dates which are in the past tense are now formatted in Green color.
- You might think that it is much easy to do this manually, but when we have larger data it will be very useful and also we can use in it on a very large scale.
Things to Remember About Conditional Formatting for Dates in Excel
- To apply this conditional formatting there are things to remember
- Before applying the formatting we have to select the columns for which the formatting needs to be applied
- While creating a new rule the formula should be applied before applying the formatting to verify the logic.
Recommended Articles
This is a guide to Conditional Formatting for Dates in Excel. Here we discussed how to use Conditional Formatting for Dates in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
