Long Standing Excel Form Suddenly Broken for Date

Question

This is beyond frustrating. I have a very simple expense form we have been using for years without fail, it is a protected workbook so cannot be changed and now suddenly not working for dates.

I had the cells formatted so that when you enter 03/11/2026 it displays as March 11, 2026. I unprotected the workbook and verified the format still exists. But when I try to type 03/11/2026, it says "This value doesn't match the data validation restrictions defined for this cell." Two other cells formatted the same way now just display the date as I typed it and doesn't format.

Why would this break after years of use? Office 2024. If anyone suggests moving to 365, save your breath it isn't happening.

Answer 1

Ok, solved (Windows 11). Go to Control Panel, select clock and region, select change date, time or number formats, select additional settings under format tab, select date at top, then type in MM/dd/yyyy and select apply then ok. It is because my region is Canada and it is not in the drop down menu for our region but can be typed manually and entered. Expense form is working again. Why they make it this difficult and don't just have it in the dropdown is beyond me. Good job MSlop.

Answer 2

So maybe this is the issue, I recently upgraded to Windows 11. When I go though Control Panel, there is no option for MM/dd/yyyy under short format.

Answer 3

The behavior is consistent with Excel no longer recognizing the typed value as a valid date under the current date system/locale and data validation rules, not with the cell’s number format itself.

Key points from the scenario:

• The workbook is protected and uses data validation on the date cells.
• Typing 03/11/2026 now triggers a data validation error.
• Other cells with the same number format no longer “re-display” the date as long text, they just show what is typed.
• This started recently on Office 2024 after years of working.

In Excel, two separate things control what is happening:

1. Whether Excel interprets the entry as a real date (depends on system date settings and date system).
2. How that date is displayed (cell’s number format).

If Excel stops recognizing the entry as a date, then:

• Data validation rules that expect a date will fail and show “This value doesn’t match the data validation restrictions defined for this cell.”
• The cell will behave as text and will not reformat to “March 11, 2026”, even if the number format is set to a long date.

From the provided documentation:

• Excel uses the computer’s date system and locale. If the typed date does not match the system’s expected pattern, Excel treats it as text and not as a date, which breaks calculations and validations that expect dates.
• Example given: if the computer displays dates as mm/dd/yyyy and a date is typed as dd/mm/yy, Excel treats it as text and not as a date.

The most likely cause is that something changed in the underlying Windows regional date settings (short date pattern) or the Excel date system for this workbook, so that the pattern 03/11/2026 is no longer interpreted as a valid date according to the validation rule.

Actionable checks and fixes based on the documentation:

1. Verify Windows short date format Excel relies on the Windows date system and short date format. If this changed (for example from MM/dd/yyyy to dd/MM/yyyy or another pattern), entries like 03/11/2026 may no longer be interpreted as expected. On Windows 10 (similar steps for 8/7):
   1. Open Control Panel.
   2. Under Clock, Language and Region, select Change date, time, or number formats.
   3. In the Region dialog, select Additional settings.
   4. Go to the Date tab.
   5. Check Short date format and ensure it uses the pattern that matches how dates are typed (for example MM/dd/yyyy if typing 03/11/2026).
   6. Confirm with OK.
   If the short date format does not match the pattern used in the form, Excel will treat the entry as text, causing validation to fail.
2. Confirm the workbook’s date system If the workbook’s date system changed (for example, the 1904 date system was turned on), dates can behave unexpectedly. In Excel:
   1. Go to File > Options > Advanced.
   2. Under When calculating this workbook, select the affected workbook.
   3. Check whether Use 1904 date system is selected. If this workbook was created on Windows and always used the default 1900 system, ensure this box is cleared.
   The documentation notes that Excel automatically switches the date system when opening documents from another platform (e.g., Mac). If that happened, it can cause date issues between workbooks.
3. Check whether the entered values are being stored as text The documentation explains that when Excel treats a date as text:
   • It aligns left by default.
   • It cannot be used in date calculations or date-based validation.
   If the date cells are left-aligned and validation fails, Excel is likely treating the entry as text due to a mismatch between the typed pattern and the system’s date format.
4. Confirm data validation rules Since the workbook is protected and uses validation, the rule is probably something like “Date between …”. If Excel no longer recognizes the input as a date (because of system format changes), the validation will reject it even though the number format looks correct. After ensuring the Windows short date format matches the expected input pattern, the same validation rule should start accepting entries again.
5. Formatting vs. interpretation The documentation distinguishes between:
   • How Excel stores dates (serial numbers based on the date system).
   • How dates are formatted (e.g., 03/11/2026 vs. March 11, 2026).
   If the cell is not recognized as a date, changing the number format (e.g., to a long date) will not change the display; it will continue to show the typed text. Once the system date settings and date system are aligned, Excel will again store the entry as a date serial value and the long date format will display correctly.

In summary, the form itself is unlikely to have “broken”; instead, a change in Windows regional date settings or the workbook’s date system means Excel no longer interprets the typed values as dates that satisfy the existing data validation rules. Restoring the original short date format and confirming the workbook’s date system should restore the previous behavior.

---

References:

• Change the date system, format, or two-digit year interpretation
• How to correct a #VALUE! error
• Format a date the way you want in Excel