Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel MMULT Function
  • Count rows with at least n matching values
  • Offset in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • How to count table rows in Excel
  • How To Filter Data in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Use Data Form to input, edit and delete records in Excel
  • Excel Line Chart

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation must contain specific text
  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • Excel Data validation must begin with

Conditional formatting date past due in Excel

by

This tutorial shows how to work Conditional formatting date past due in Excel using the example below;

Formula

=A1<TODAY()

Explanation

To highlight dates that are “past due” you can use a conditional formatting and a basic formula to check whether each date is less than today. In the example shown, conditional formatting has been applied to the range C5:C12 with this formula:

=C5<TODAY()

When a date in column C is less than the date TODAY (i.e. past due), the formula returns TRUE and triggers the rule.

Worked Example:   Highlight duplicate columns in Excel

Note: conditional formatting formulas should be entered relative to the “active cell” in the selection, which is assumed to be C5 in this case.

How this formula works

When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is assumed to be cell C5, with the range C5:C12 selected.

Worked Example:   How to Calculate Age in Excel

As the formula is evaluated, formula references change so that the rule is testing for blank values in the correct row for each of the 8 cells in the range:

=C5<TODAY() // C5
=C6<TODAY() // C6
=C7<TODAY() // C7
etc.

Since Excel dates are by just serial numbers, any past due date is, by definition, less than the current date, which isgenerated with the TODAY function. This article was created December 17, 2018, so all earlier dates return TRUE and are highlighed by the rule. Dates greater than or equal today return FALSE so no shading is applied.

Worked Example:   Highlight unprotected cells in Excel

Overdue by at least n days

To test if dates are overdue by at least N days, you can use this formula:

=(TODAY()-C5)>=N

This formula will trigger conditional formatting only when a date in column C is at least n days earlier than today.

Post navigation

Previous Post:

Highlight 3 smallest values with criteria in Excel

Next Post:

Conditional formatting dates overlap in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to convert text string to array in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • How to extract text between parentheses in Excel
  • How to strip non-numeric characters in Excel
  • How to check if cell contains all of many things in Excel
  • Get fiscal quarter from date in Excel
  • Get work hours between dates custom schedule in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Generate series of dates by weekends in Excel
  • Convert text timestamp into time in Excel
  • How to Calculate Tax Rates in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles