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

Data Analysis

  • How to create running total in an Excel Table
  • Understanding Pivot Tables in Excel
  • How to create a Histogram in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Excel Line Chart

References

  • How to use Excel OFFSET function
  • How to use Excel FORMULATEXT function
  • Convert text string to valid reference in Excel using Indirect function
  • Multi-criteria lookup and transpose in Excel
  • Count rows with at least n matching values

Data Validations

  • Excel Data validation exists in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Excel Data validation number multiple 100
  • Excel Data validation no punctuation

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:

VLOOKUP with two client rates in Excel

Next Post:

How to Create Gantt Chart 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

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • Find Last Day of the Month in Excel
  • Add business days to date in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Basic timesheet formula with breaks in Excel
  • Convert Unix time stamp to Excel date

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign people to groups in Excel

General

  • Create dynamic workbook reference to another workbook in Excel
  • How to password protect excel sheet?
  • How to get original number from percent change in Excel
  • Excel Default Templates
  • Share Excel data with Word documents
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning