Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Insert and Customize Sparklines in Excel
  • Chart Axes in Excel
  • Managing Conditional Formatting Rules in Excel
  • How To Compare Two Lists in Excel
  • How to Create One and Two Variable Data Tables in Excel

References

  • Left Lookup in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Multi-criteria lookup and transpose in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to get first row number in range in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Data validation must not exist in list
  • Excel Data validation unique values only

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.

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.

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.

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:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

Logical Functions

  • Nested IF function example in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Calculate years between dates in Excel
  • Get days between dates in Excel
  • How to get year from date in Excel
  • Convert Excel time to decimal seconds
  • Steps to create Dynamic calendar grid in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel
  • If cell contains one of many things in Excel

General

  • AutoRecover file that was never saved in Excel
  • List worksheet index numbers in Excel
  • Flash Fill in Excel
  • Subtotal invoices by age in Excel
  • Convert column number to letter in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning