Fix Excel Macro-Enabled Workbook Rejecting Dates After 2020
Question details
The user needs to identify why a macro-enabled Excel workbook rejects valid dates and correct the rule or code responsible.

- 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.
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.
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.
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.
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.
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.
Enter the rejected date again in the test copy. If it is still rejected, check the date format and macro code.

Check how Excel interprets the entered date
Use this check when the validation rule looks correct but the date may be entered as text or in an unexpected format.
Inspect workbook macros for date restrictions
Use this method if worksheet validation does not explain the error or if the message appears to come from a macro.
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.

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.




