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

Data Analysis

  • Remove Duplicates Example in Excel
  • How to do a t-Test in Excel?
  • Excel Frequency Function Example
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to add Trendline to a chart in Excel

References

  • Vlookup Examples in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Count rows that contain specific values in Excel
  • How to get last row in numeric data in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation with conditional list
  • How To Create Drop-down List in Excel
  • Excel Data validation whole percentage only
  • Data validation must not exist in list

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:   Conditional formatting gantt chart weekends 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:   Highlight multiples of specific value 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:   Managing Conditional Formatting Rules 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

Logical Functions

  • How to use Excel AND Function
  • Not Equal To ‘<>‘ operator in Excel
  • IF with wildcards in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • How to get year from date in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Count times in a specific range in Excel
  • Sum race time splits in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel

General

  • Convert column number to letter in Excel
  • How to create dynamic named range with INDEX in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to calculate profit margin percentage in Excel
  • Excel Autofill Cell Ranges, Copy, Paste
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning