EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Conditional Formatting for Dates
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel Conditional Formatting for Dates

By Madhuri ThakurMadhuri Thakur

Conditional Formatting for Dates in Excel

Conditional Formatting for Dates in Excel (Table of Contents)

  • 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 as per criteria set by you. Here we will talk about formatting dates to 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.

Start Your Free Excel Course

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,368 ratings)

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

Conditional Formatting For Dates Path 1-2

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

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

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 of just highlighting the dates from the last week.

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

Conditional Formatting For Dates Data 1

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

Conditional Formatting For Dates Examples 1

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

Conditional Formatting For Dates Example 1-2

  • As we can see from the below screenshot, there are two drugs that have the expiry date of next week, which is highlighted with Light Red fill 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 need to explore making new rules as there is no built-in option available to highlight the dates of the weekend. Suppose we have data of the drug with its expiry dates as follows.

Conditional Formatting For Dates Data

  • 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

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, we can say three expiry dates are at weekends.

Example #3 – Highlight the Dates Past Today

Now, suppose we have the data for this example as per the below image.

Conditional Formatting For Dates Data 3

  • We want to highlight the dates that 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 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, in that select 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 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. Use Of Conditional Formatting In MS Excel
  2. Excel Conditional Formatting for Dates
  3. Compare Dates in Excel
  4. Date Formula in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Conditional Formatting for Dates Excel Template

EDUCBA

Download Conditional Formatting for Dates Excel Template

EDUCBA

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

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more