EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Excel Conditional Formatting for Dates
 

Excel Conditional Formatting for Dates

Madhuri Thakur
Article byMadhuri Thakur

Updated June 12, 2023

Conditional Formatting for Dates in Excel

 

 

Conditional Formatting for Dates in Excel (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Conditional Formatting for Dates in Excel
  • How to Use Conditional Formatting for Dates in Excel?

Conditional Formatting for Dates in Excel

Conditional formatting for dates in Excel means it formats the particular data per your criteria. Here we will discuss formatting dates to highlight/format the dates we want in a particular format. The selected dates, i.e., the dates before today, dates of Sundays, weekends, and many more.

Here in Excel, we can select the rules already there or make our own rule and make our desired condition to find/format the desired data. First, 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 the below screenshot.

Conditional Formatting For Dates Path 1-1

  • The above screenshot shows the options available for conditional formatting by clicking the conditional formatting from the toolbar.
  • Now the options available for us are many, but here we have to use them to format the dates.

Step: 3

  • From the screenshot, we can see a sub-option available of A Date Occurring under the category of highlighting cell rules; clicking the box below will open to show you some options to highlight the particular dates.

Conditional Formatting For Dates Path 1-2

  • Here we have an option 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. Like the color of the text and the colored cell, there are some fixed options. Kindly see the following image for the reference of the options available.

Conditional Formatting For Dates Path 1-3

  • 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 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 in situations like Found duplicate dates or highlighting the required dates of the week by making formula, and we can highlight the dates which are on Monday, Sunday or any other day of the week.

Using built-in options, we can highlight the dates of Yesterday, Today Next Month, or if the built-in option is not usable for us, we can make our own rules. We can format the data as we want using formulas like a weekday, today, date, and Now.

You can download this Conditional Formatting for Dates Excel Template here – Conditional Formatting for Dates Excel Template

Example #1 – Highlight the Dates of Next Week

For this example, we will see the basic example highlighting last week’s dates.

Suppose we have data on the drug with its expiry dates as follows.

Conditional Formatting For Dates Data 1

  • Now we will select all dates, go to the conditional formatting tool from the Home menu, and select the A Date Occurring.

Conditional Formatting For Dates Examples 1

  • As we want the next week’s dates, we have selected the “Next week” option, and for formatting, we have selected the “Light Red Fill with Dark Red text.”

Conditional Formatting For Dates Example 1-2

  • As we can see from the screenshot below, two drugs have an expiry date of next week, highlighted with Light Red and filled with Dark Red text as required.

Conditional Formatting For Dates Example 1-3

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 must explore making new rules, as no built-in option exists to highlight the weekend dates. Suppose we have data on the drug with its expiry dates as follows.

Conditional Formatting For Dates Data

  • As no built-in option is available to highlight the weekends, we need to make a rule which identifies the weekend from the given dates and then format and highlight 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

Conditional Formatting For Dates Example 2-1

  • The output is given below.

Conditional Formatting For Dates Example 2-2

  • Now repeat the steps, select the date cells C2 to C9, and now click on Conditional Formatting.
  • Click on New Rule.

Conditional Formatting For Dates Example 2-3

  • 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.

Example 2-4

  • A box, “Format Cells,” will open, and we can fill any color we want.

Example 2-5

  • Here we have taken Green color; by clicking ok, we will have our weekend dates available in Green filling as per the below image.

Example 2-6

  • As per the results, three expiry dates are at weekends.

Example #3 – Highlight the Dates Past Today

Suppose we have the data for this example as per the image below.

Conditional Formatting For Dates Data 3

  • We want to highlight the dates past today (09/04/2019), so which dates 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 the =NOW() function

The steps will be as follows:

  • Select the Expiry Dates from the column.
  • Click on the Conditional Formatting and click on New Rule.

Conditional Formatting For Dates Example 3-1

  • A box will open, selecting the Rule type Format only Cells that contain.
  • In the rule description, select Cell Value in the first box and Less than in the second box, and type =NOW() function in the third box.

Example 3-2

  • Now click on format and select the color format according to your choice, and we’re done.

Example 3-3

  • Here, for example, we have used a 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.

Example 3-4

  • You might think that it is much easier to do this manually, but when we have larger data, it will be very useful, and also, we can use 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 using conditional formatting for Dates in Excel and practical examples, and a downloadable Excel template. You can also go through our other suggested articles to learn more –

  1. Excel Conditional Formatting for Dates
  2. Compare Dates in Excel
  3. Excel DATE Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Conditional Formatting for Dates Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Conditional Formatting for Dates Excel Template

EDUCBA

डाउनलोड Conditional Formatting for Dates Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW