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

Data Analysis

  • How to sum a total in multiple Excel tables
  • Get column name from index in Excel Table
  • How To Compare Two Lists in Excel
  • How to Create Area Chart in Excel
  • Get column index in Excel Table

References

  • How to use Excel ROW Function
  • Create hyperlink with VLOOKUP in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • Merge tables with VLOOKUP in Excel
  • How to use Excel MMULT Function

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text
  • Excel Data validation no punctuation
  • Excel Data validation date in next 30 days

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

  • How to return blank in place of #DIV/0! error in Excel
  • Return blank if in Excel
  • Invoice status with nested if in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • Add workdays to date custom weekends in Excel
  • Get last working day in month in Excel
  • Display Days until expiration date in Excel
  • Calculate years between dates in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • Group numbers at uneven intervals in Excel

General

  • Zoom Worksheet in Excel
  • Count cells that contain errors in Excel
  • How to calculate percent of students absent in Excel
  • Excel Autofill Cell Ranges, Copy, Paste
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning