Fix Excel VBA Dates Stored as Text for Conditional Formatting
Question details
The user needs Excel VBA to write a true date value so date-based conditional formatting works correctly.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro writes today’s date to a worksheet and the user wants to apply date formatting and conditional formatting based on that value.
- Observed behavior
- The VBA Format function writes a formatted date string as text, so Excel does not evaluate it as a date for conditional formatting.
Identify the VBA procedure that writes the date and confirm which worksheet column receives the value. Check the target cells’ Number Format so you can separate the stored date value from its displayed appearance.
Write a real date value instead of formatted text
Use this fix when VBA currently assigns Format(Date, ...) and Excel conditional formatting does not recognize the result as a date.
The Format function returns text. Assign Date directly to the cell so Excel stores its underlying date serial value, then apply the desired display format separately.
Press Alt+F11 to open the Visual Basic Editor, locate the worksheet or standard-module procedure that writes the date, and find the line using Format(Date, ...).
Replace `Target.Offset(0, 1) = Format(Date, "dd/mm/yyyy")` with `Target.Offset(0, 1) = Date`. This stores the current date as a real Excel date value.
Return to Excel, select column B or the destination date range, open Home > Number Format or Format Cells, and choose the required date format such as `dd/mm/yyyy`.
Select the date range, choose Home > Conditional Formatting > New Rule, and create a date-based rule such as a cell value less than `=TODAY()+7`.
Use WPS Spreadsheet for Reliable Date Formatting and Conditional Rules
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface, strong Excel file compatibility, and simple tools for formatting date values and creating conditional formatting rules. It supports seamless migration for everyday spreadsheet work, while VBA-specific macros may still require Microsoft Excel.
Frequently Asked Questions
Why does Format(Date, "dd/mm/yyyy") prevent conditional formatting from working?
Format returns a text string, not a date value. Excel may display it like a date, but date comparisons and rules cannot reliably evaluate it as a real date.
What VBA code should store today’s date in the worksheet?
Use `Target.Offset(0, 1) = Date`. Apply the display format to the destination cells separately instead of converting the date to text in VBA.
How do I keep the date displayed as dd/mm/yyyy?
Select the date column, open Home > Number Format or Format Cells, choose Custom, and enter `dd/mm/yyyy`. This changes the display while preserving the underlying date value.
What conditional formatting formula highlights dates within seven days?
Select the date range and use Home > Conditional Formatting > New Rule with a formula such as `=A1<TODAY()+7`, replacing A1 with the first cell in your selected range.
Does changing the cell format convert existing text dates into real dates?
No. Number formatting changes appearance only. Existing text dates may need conversion through Data > Text to Columns, a formula such as DATEVALUE, or VBA before date-based rules can evaluate them correctly.
