digestVidSign in
summary

excelisfun - Excel Magic Trick 1173: Conditional Format Dates When they are 90 Days of Set Expiration Date

Thumbnail of the video “excelisfun - Excel Magic Trick 1173: Conditional Format Dates When they are 90 Days of Set Expiration Date”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: Use conditional formatting with formulas to color expiration dates by time remaining: green for more than 90 days away, yellow for 90 down to 1 day, and red for expired or due today (zero days or fewer). The formulas return TRUE or FALSE for each cell, and those results drive the three colors, so the formatting updates automatically when the expiration date changes.

Key Points

  • Excel has no built-in rule for this three-tier, 90-day scheme, so the video builds it with logical (TRUE/FALSE) formulas.
  • The first step is calculating days remaining by subtracting each date from the expiration date, with the expiration cell locked using F4 (absolute reference).
  • A comparison formula such as "less than or equal to 90" returns TRUE for dates in the yellow window, and "less than or equal to 0" returns TRUE for red.
  • Three rules are applied: a default green fill for the whole range, then a yellow rule and a red rule layered on top; the red rule ends up above the yellow rule in Manage Rules, so it takes precedence.

Details Worth Noting

  • Concrete instructions:
    • In a helper column, enter the days-remaining formula: type =, click the expiration date cell, press F4 to lock it, type a minus sign, then click the date cell in the row and press Ctrl+Enter. Fill it down.
    • In the next column, enter =(days cell)<=90 with the days reference locked as needed, press Ctrl+Enter, and fill down to view TRUE/FALSE results for yellow.
    • Copy that formula, change the 90 to 0, and fill it down to create the TRUE/FALSE pattern for red.
    • Select the full date range with the active cell at the top. Apply green as the base fill through Home, Fill Color, and set the font to a light color.
    • Open Home, Conditional Formatting, New Rule (or Alt, H, L, N). Choose "Use a formula to determine which cells to format," paste the yellow formula, and set a yellow fill with black font.
    • Create a second New Rule with the red formula (less than or equal to 0), set a red fill and white font, and confirm the red rule sits above the yellow rule in Manage Rules so it takes precedence.
    • Delete the helper columns when finished, or keep them for checking.
  • The example uses a 90-day threshold; changing the expiration date or the threshold updates the colors dynamically.
  • The presenter notes the dark green fill may need a light font, and default white fonts need changing to black for the yellow rule.
  • Ctrl+Z is used to undo test edits so the sample workbook stays as built.

Bottom Line

Formula-driven conditional formatting turns days-to-expiration into green, yellow, and red status colors that update automatically, giving a clear visual warning system without manual recoloring.