Fix Incorrect Date Formats in Excel Data-Validation Lists
Question details
The user needs to troubleshoot why a date selected from an Excel data-validation drop-down in a shared SharePoint workbook displays differently for one user or causes macros to fail.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user selects a date from an Excel data-validation list in a SharePoint workbook, and the displayed date format differs from other users or affects macro execution.
- Observed behavior
- The selected date appears in an unexpected format, or macros fail because the underlying date value or display format does not match the expected input.
Before changing formats, open the shared workbook in the same spreadsheet application your colleagues use and confirm you can edit the validated cell. Note the expected date format and whether the validation source contains real date serial values or text entries.
Align Regional Date Settings and Reapply the Date Number Format
Use this first when the same shared workbook displays dates differently on one computer, because the operating system short-date setting often drives the default date appearance.
Spreadsheet applications store real dates as serial numbers, and the cell number format controls how that serial number is displayed. If the operating system short-date format differs between users, the same date can look inconsistent even when the underlying value is correct.
Open Windows Settings > Time & Language > Region, or Control Panel > Region, and note the Short date format used by your computer and a colleague's computer.
In WPS Office Spreadsheets, right-click the validated date cell and choose Format Cells, or press Ctrl+1.
On the Number tab, select Date in Category, choose the required date format from the Type list, and click OK.
Click the validated cell, open the data-validation list, select the same date, and confirm that the displayed value now matches the expected format.

Verify That the Validation Source Contains Real Date Serial Values
Use this when dates look like text or macros fail because the selected value is not treated as a date.
Test the Workbook and Compare Excel, Add-in, and SharePoint Settings
Use this when format and source values look correct but the issue remains, especially if macros still fail.
Fix Excel Data-Validation Date Formatting in WPS Office Spreadsheets
Open the shared workbook in WPS Office Spreadsheets, check the data-validation source and cell number format, and reapply the required date format so the selected value matches the display standard and macro expectations.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the downloaded Excel workbook from SharePoint.
- 2. Inspect the data-validation source: Select the validated cell, click Data > Validation, and check whether the Source range contains serial dates or text values.
- 3. Reapply the date format: Right-click the cell, choose Format Cells > Number > Date, or press Ctrl+1, then select the required date format.
- 4. Test the selection and macro: Choose a value from the drop-down, confirm the formula bar shows the correct serial date, and run the macro or share the file to verify the format.

Frequently Asked Questions
Why does the same Excel date drop-down show a different date format on another computer?
The usual cause is a different operating system short-date setting. Open Windows Settings > Time & Language > Region or Control Panel > Region, set the same Short date format as your colleagues, and reapply the cell number format.
How can I check whether the data-validation list contains real dates instead of text?
Select the source cells and look at the formula bar. Real dates display as date serial numbers with a date format, while text such as "Jan '24" remains text. Replace text entries with actual dates and apply a date number format.
Will clearing cell formatting remove data validation?
Clearing only the format does not remove data validation. To be safe, use Format Cells > Number > Date to reapply the required format; avoid Clear All, which can remove validation.
Why does my macro fail after selecting a date from the drop-down?
The macro may expect a Date/Serial value, but the source may contain text or the selected cell may have an unexpected format. Verify that the source uses real date serial values and that the selected cell format matches the macro's expected input.
Can WPS Office open and fix SharePoint Excel files with this date format issue?
Yes. WPS Office Spreadsheets opens .xlsx files from SharePoint, lets you inspect data validation and cell formats, and reapply the correct date format while preserving Microsoft Excel compatibility.




