Function Problems

How to Create an Excel Color-Coded Reorder Alert System

Tauseeq Magsi Oct 22, 2026 868 views

Question details

The user wants to set up a system to automatically calculate reorder dates and apply color-coded alerts (green, yellow, red) based on proximity to the reorder deadline.

How to Create an Excel Color-Coded Reorder Alert System

Product: Excel
Device & OS: not provided
Scenario: Managing inventory and tracking supplier reorder dates to maintain optimal stock levels.
Observed behavior: A functioning alert system that automatically updates status colors to indicate when a reorder is upcoming, due, or overdue.

Before you start

Ensure your spreadsheet has dedicated columns with valid numerical data for the last order date, reorder frequency (in days), and supplier lead time (in days) before applying the formulas.

Solution 1Recommended

Calculate Reorder Dates and Apply Conditional Formatting

Use date formulas and conditional formatting to automatically flag inventory items as Green, Yellow, or Red.

This solution utilizes basic date arithmetic to determine the exact reorder date, followed by a nested IF statement that compares the reorder date to the current date using the TODAY() function.

Calculate the Reorder Date

In your Reorder Date column (for example, cell E2), enter the formula to calculate the next date: =B2+C2-D2 (assuming B2 is Last Order Date, C2 is Reorder Frequency, and D2 is Lead Time). Press Enter.

Determine the Alert Status

In your Status column (for example, cell F2), enter the nested IF formula: =IF(TODAY()-E2>2,"Red",IF(TODAY()-E2>=-2,"Yellow","Green")). This classifies the urgency based on today's date.

Apply Formulas to the Entire List

Select the cells containing your new formulas. Click and drag the fill handle (the small square at the bottom-right corner of the cell) down to copy the formulas to the rest of your inventory list.

Set Up Conditional Formatting

Select the Status column. Go to the 'Home' tab on the ribbon, click 'Conditional Formatting', and navigate to 'Highlight Cells Rules' > 'Equal To'. Create three separate rules: one for "Red" (format with light red fill), one for "Yellow" (format with yellow fill), and one for "Green" (format with green fill).

Calculate Reorder Dates and Apply Conditional Formatting

Dynamic Updates: Because the status formula uses the TODAY() function, your status indicators will automatically recalculate and update every time you open the workbook.

Manage Inventory Effortlessly

Build Your Reorder Alert System in WPS Spreadsheet

Easily track inventory, calculate dates, and apply conditional formatting with WPS Spreadsheet. It offers an intuitive interface and complete formula support for managing your business data effectively.

1. Open WPS Spreadsheet: Launch WPS Office and open your inventory document or start a new blank spreadsheet.
2. Enter Your Formulas: Input your date calculation and nested IF formulas exactly as you would in Microsoft Excel to generate your text statuses.
3. Apply Conditional Formatting: Navigate to the Home tab, click Conditional Formatting, and easily set up your Green, Yellow, and Red cell highlighting rules based on the cell values.

Fully compatible with Microsoft Excel (.xlsx) formats and nested formulasEasy-to-use conditional formatting tools for quick visual alertsBuilt-in templates for inventory and stock managementLightweight application that runs smoothly across Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why is the TODAY() function showing the wrong date in my formulas?

The TODAY() function relies on your computer's system clock. If the date is incorrect, check your operating system's date and time settings and ensure they are synced to the correct time zone.

Can I use icon sets instead of cell background colors for the alerts?

Yes. Instead of using 'Highlight Cells Rules', you can select your status column, go to Conditional Formatting on the Home tab, choose 'Icon Sets', and apply traffic light icons based on the calculated values.

How do I exclude weekends from my supplier lead time calculation?

To calculate reorder dates using only working days, replace simple addition and subtraction with the WORKDAY function, such as =WORKDAY(B2, C2-D2), which automatically skips weekends.

Excel & Data Problems