Formatting Issues

How to Set Up Expiry Date Alerts and Color Coding in Spreadsheets

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs a method to set up automated alerts in a spreadsheet to track pharmacy inventory, specifically triggering notifications or color coding at six months, three months, and on the exact expiry date.

Product: Spreadsheet
Device & OS: not provided
Scenario: Tracking pharmacy inventory expiration dates to ensure timely stock rotation and safe disposal.
Observed behavior: The user is looking to implement date-based color coding and automated email alerts for upcoming expiration milestones.

Before you start

Ensure your inventory expiry dates are correctly formatted as date values (not text) in your spreadsheet, and identify the starting cell of your date column.

Solution 1Recommended

Use Conditional Formatting for Date-Based Color Coding

Apply conditional formatting using the EDATE and TODAY functions to automatically highlight cells as they approach expiration.

Conditional formatting dynamically changes the color of a cell based on its value. By combining the TODAY() function (which returns the current date) with EDATE() (which calculates months in the future or past), you can create multi-tiered color alerts for your pharmacy inventory.

Select the Target Cells

Highlight the entire column or specific range containing your expiry dates (for example, A2:A100).

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.

Set the 6-Month Alert Rule

Choose 'Use a formula to determine which cells to format'. Enter the formula =AND(TODAY()>=EDATE(A2,-6),TODAY()<EDATE(A2,-3)). Click 'Format', choose a yellow fill color, and click 'OK'.

Set the 3-Month Alert Rule

Create another new rule using the formula =AND(TODAY()>=EDATE(A2,-3),TODAY()<A2). Format these cells with an orange fill color to indicate higher urgency.

Set the Expired Alert Rule

Add a final rule using the formula =TODAY()>=A2. Set the format to a red fill color to highlight inventory that has already expired or is expiring today.

Dynamic Updates: The TODAY() function automatically updates every time you open the spreadsheet, ensuring your color codes always reflect real-time expiration statuses.

Solution 2

Automate Email Alerts Using Power Automate

Use an external automation tool to send email notifications based on spreadsheet data if visual color-coding is not sufficient.

Manage Inventory Efficiently with WPS Office

Track Expiry Dates Seamlessly in WPS Spreadsheets

WPS Spreadsheets provides robust conditional formatting features fully compatible with standard formulas. You can effortlessly track pharmacy inventory and highlight expiration dates without complex configurations.

1. Open Your Inventory Data: Launch WPS Spreadsheets and open your pharmacy inventory file.
2. Highlight the Date Range: Select the column containing your expiry dates.
3. Access Conditional Formatting: Go to 'Home' > 'Conditional Formatting' > 'New Rule'.
4. Apply the Formula: Select formula-based formatting, input =AND(TODAY()>=EDATE(A2,-6),TODAY()<=A2), and pick a warning color.

Fully compatible with Microsoft Excel formulas like EDATE and TODAY().Intuitive conditional formatting interface to color-code inventory thresholds.Free, lightweight, and fast alternative to heavy spreadsheet tools.Supports seamless cross-device synchronization for mobile inventory checking.

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens when the cell reference in your formula does not match the first cell of your selected range. Ensure that if your formula uses A2, your selected highlight range starts exactly at row 2.

Can I calculate expiration dates without using the EDATE function?

Yes, you can subtract days directly from the date reference (e.g., =A2-TODAY()<=180 for roughly 6 months). However, using the EDATE function is recommended because it accounts for varying month lengths accurately.

How do I clear or edit existing conditional formatting rules?

Select the formatted cells, navigate to 'Home' > 'Conditional Formatting' > 'Manage Rules'. From there, you can edit the formulas or delete the rules entirely.

Will using the TODAY() function slow down my spreadsheet?

TODAY() is a volatile function, meaning it recalculates every time a change is made. While it might cause minor delays in sheets with hundreds of thousands of rows, the performance impact on standard pharmacy inventory lists is negligible.

Document & File Issues