Data Import & Export

Fix Excel Macro-Enabled Workbook Rejecting Dates After 2020

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

The user needs to identify why a macro-enabled Excel workbook rejects valid dates and correct the rule or code responsible.

Fix an Excel Macro-Enabled Workbook That Rejects Dates After 2020
Product
Microsoft Excel
Device & OS
not provided
Scenario
Entering a valid date in a macro-enabled workbook that began rejecting dates on January 1, 2020.
Observed behavior
The workbook displays an incorrect date format error when the user enters a date.
Before you start

Identify an affected cell and a date that triggers the error. Use a sanitized test copy if the workbook contains sensitive data, and enable macros only if you trust the workbook.

Solution 1Recommended

Check the affected cells' date validation rules

Use this method to find a date limit, custom formula, or separate validation rule that rejects the entry.

1
Inspect the validation rule

Select an affected cell, then choose Data > Data Validation. On the Settings tab, check the Allow field and any start date, end date, or custom formula.

2
Check date limits and references

Review any fixed cutoff dates or cell references in the validation criteria. If a limit is outdated, update it to the intended range and select OK.

3
Compare nearby cells

Select other affected cells or ranges and open Data > Data Validation again. Look for different rules applied to parts of the column rather than assuming the entire column uses the same rule.

4
Test the entry

Enter the rejected date again in the test copy. If it is still rejected, check the date format and macro code.

Check the affected cells' date validation rules
Check custom formulas: A custom validation formula may refer to another cell that contains a date limit. Inspect the referenced cell before changing the formula.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not repair Excel-specific validation or VBA issues, so test macro-dependent workbooks before migrating.

Review and edit spreadsheet data in a familiar interfaceOpen common Microsoft Excel formats, including XLS and XLSXUse data validation features for spreadsheet entry rulesWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why would a workbook reject dates starting in 2020?

A validation rule or macro may contain a fixed date limit, but the cause must be checked in the workbook. Select an affected cell and open Data > Data Validation, then inspect relevant VBA code if the rule does not explain the error.

Can different cells in the same column have different validation rules?

Yes. Select cells from different affected ranges and open Data > Data Validation for each to compare their criteria.

Will changing the cell's date format fix the error?

Not necessarily. Home > Number Format changes how a date is displayed, but it does not update validation limits or macro code. Also check whether Excel recognizes the entry as a date rather than text.

How can I tell whether a macro is causing the message?

In a sanitized test copy, compare the behavior with macros disabled, then inspect trusted workbook code through Developer > Visual Basic. Search for the error text and date checks. Some workbook features may not work while macros are disabled.