How to Apply Conditional Formatting to Dates with Text in Excel
Question details
The user needs to apply conditional formatting based on dates in cells that also contain text notes, such as "28/05/24 Approved".
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to use date arithmetic and conditional formatting rules on cells containing mixed data types (dates and text strings).
- Observed behavior
- The cell is evaluated as text instead of a valid date, causing date arithmetic and conditional formatting rules to fail or behave unreliably.
Determine whether your spreadsheet structure allows for adding new columns, as separating the date from the text is the most reliable way to perform date arithmetic.
Separate the Date and Text into Different Columns
The most reliable approach is to keep dates and text notes in separate cells so that Excel can evaluate the date values natively.
Excel stores dates as serial numbers. When text is appended to a date in the same cell, Excel treats the entire cell as a text string, breaking any date-based formulas or conditional formatting rules.
Insert a new column for 'Status' or 'Comments' next to your existing date column.
Move the text (e.g., 'Approved') into the new column, leaving only the pure date (e.g., '28/05/24') in the original column.
Select the cleaned date column, go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format' and enter a rule to evaluate the date, such as =AND(ISNUMBER(D6), D6-TODAY()>=60).
Extract the Date Using a Helper Column
If you cannot alter the original data containing both date and text, use a helper column to extract the date for conditional formatting.
Effortlessly Manage Data and Conditional Formatting in WPS Office
WPS Spreadsheet provides powerful data extraction tools and intuitive conditional formatting to help you manage complex datasets, including mixed text and dates, with ease.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your mixed date and text cells.
- 2. Split the mixed data: Select the mixed column, navigate to the 'Data' tab, and click 'Text to Columns' to quickly separate the dates from the text into adjacent columns.
- 3. Access Conditional Formatting: Highlight the newly isolated date cells, go to the 'Home' tab on the ribbon, and click 'Conditional Formatting'.
- 4. Create a new rule: Choose 'New Rule', select 'Use a formula to determine which cells to format', and input your date-based formula to apply the desired cell styling.
Frequently Asked Questions
Why does conditional formatting fail when dates have text in the same cell?
Excel recognizes dates as numeric serial values. When letters or words are added to the same cell, the software converts the entire cell's contents into a text string. Because it is no longer recognized as a number, mathematical operations and date functions like TODAY() cannot process it.
Can I format a cell to show text but still act as a date?
Yes. Instead of typing text into the cell, you can apply a Custom Number Format to a pure date. Select the cell, press Ctrl+1, go to Custom, and type a format like dd/mm/yy "Approved". This displays the text visually, but the underlying data remains a valid date that conditional formatting can evaluate.
What formulas can I use to highlight upcoming dates?
If your pure date is in cell D6, you can use the formula =AND(ISNUMBER(D6), D6-TODAY()>=1, D6-TODAY()<60) in the conditional formatting rule. This specific formula will highlight the cell if the date occurs within the next 60 days.
