Function Problems

How to Use Excel Conditional Formatting for Expiry Dates with Extra Text

Nimra Malik Sep 27, 2026 870 views

Question details

The user needs to conditionally format cells that contain a mix of text and dates, specifically a reference ID, a line break, and an expiry date.

How to Use Excel Conditional Formatting for Expiry Dates with Extra Text

Product: Excel
Device & OS: not provided
Scenario: Highlighting expired dates in a dataset where the date is located at the end of a multi-line text string within a single cell.
Observed behavior: A custom formula is required to extract the date from the text string, convert it to a recognizable date value, and evaluate if it has passed today's date to trigger the conditional formatting.

Before you start

Ensure your cells use a consistent delimiter, such as a line break, before the expiry date so the formula can accurately identify and extract the date string.

Solution 1Recommended

Extract and Format Using TEXTAFTER and DATEVALUE

Use a custom conditional formatting formula to isolate the date from the text string and compare it to the current date.

By utilizing the TEXTAFTER function, you can extract everything that appears after the line break (represented by CHAR(10)). The DATEVALUE function then converts this extracted text into a proper date serial number that Excel can compare against TODAY().

Select the target range

Select the range of cells you want to format. Make sure you note the active cell (e.g., cell D2), as this will be used in your formula.

Open Conditional Formatting

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

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter: =DATEVALUE(TEXTAFTER(D2,CHAR(10)))<TODAY()

Apply formatting style

Click the 'Format' button, navigate to the 'Fill' tab, and choose your desired highlighting style (such as a red fill for expired items). Click OK to confirm and apply the rule.

Extract and Format Using TEXTAFTER and DATEVALUE

Version Compatibility: The TEXTAFTER function is available in newer versions of Excel and Microsoft 365. If you are using an older version, you may need to use a combination of RIGHT, LEN, and FIND functions instead.

Manage Dates Efficiently with WPS Spreadsheet

Highlight Expiry Dates Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides robust formula support and intuitive conditional formatting, making it easy to manage complex cell data, extract dates, and automate your workflow.

1. Open your dataset: Launch WPS Spreadsheet and select the column containing your mixed text and date values.
2. Access Conditional Formatting: Go to the 'Home' tab on the top menu, click 'Conditional Formatting', and choose 'New Rule'.
3. Input the extraction formula: Select the formula option and input your date extraction formula (e.g., using RIGHT, LEN, and FIND for broad compatibility) to evaluate against the current date.
4. Set color and apply: Set your preferred fill color for expired items and click 'OK' to instantly apply the visual highlights.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Processes complex array and text-extraction formulas instantly.Intuitive and familiar user interface for managing advanced custom formatting.Completely free to use for everyday spreadsheet and office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

What if my cell uses a space or comma instead of a line break?

You can easily adjust the delimiter in the TEXTAFTER function. For example, replace CHAR(10) with ", " for a comma and space, or " " for a single space.

Why is the DATEVALUE function returning a #VALUE! error?

DATEVALUE requires the extracted text to be in a recognizable date format. If your date is formatted in an unsupported regional style or contains extra hidden spaces, it will return an error. Wrapping the extracted text in the TRIM function can help remove hidden spaces.

How do I highlight dates that will expire in the next 30 days?

You can modify your conditional formatting formula to check if the date falls within the next 30 days using the AND function: =AND(DATEVALUE(TEXTAFTER(D2,CHAR(10)))>=TODAY(), DATEVALUE(TEXTAFTER(D2,CHAR(10)))<=TODAY()+30).

Can I extract the date if I don't have the TEXTAFTER function?

Yes, you can use traditional text functions. For a line break delimiter, use the formula: =DATEVALUE(RIGHT(D2, LEN(D2) - FIND(CHAR(10), D2))) < TODAY() to achieve the same result in older software versions.

Excel & Data Problems