Improper Date Format from Data Validation List

Copper Contributor

Hello Community!


For some odd reason, I am the only member of a team with this issue from a SharePoint excel file.  The excel file has a data validation list from a drop-down window of dates to select.  When I select the date/year option, the list returns the wrong date format which causes the macros in the excel file to blow up and not properly execute (e.g. list has Jan '24 to populate January); however, the date that returns for me is Jan '24, not January that causes the macro to work.  I am the only team member that has this problem.  I checked the cell format against other team members and it appears the cell is formatted the same.  Attached is a copy of the date cell in red.  I have also attached the cell formula that works for other team mates.  Please help!  Oh, btw, the IT Helpdesk 






1 Reply


If you are encountering issues with date formatting when using a data validation list in Excel, it is possible that the problem lies in the regional settings or date formatting options on your computer. Here are some steps you can take to troubleshoot and potentially resolve the issue:

  1. Check Regional Settings:
    • Ensure that your computer's regional settings are configured correctly. Date formats can be influenced by the regional settings on your computer.
    • Go to your computer's Control Panel or Settings, and check the Region or Language settings to make sure they match the desired date format.
  2. Excel Options:
    • Open Excel and go to "File" > "Options."
    • In the Excel Options dialog, navigate to the "Advanced" section.
    • Scroll down to the "Editing options" section and check the option that says "Extend data range formats and formulas." This setting can sometimes affect how Excel interprets dates.
  3. Clear Cell Format:
    • Clear the cell format of the problematic date cell and then reapply the desired date format.
      • Select the cell with the date.
      • Go to the "Home" tab and choose "Clear" from the "Editing" group. Select "Clear Formats."
      • Then, reformat the cell as a date by selecting the cell, right-clicking, choosing "Format Cells," and setting the desired date format.
  4. Check Excel Version:
    • Ensure that you and your team members are using the same version of Excel. Date formatting can vary slightly between different Excel versions.
  5. Test with a Different Workbook:
    • Create a new Excel workbook and set up a simple data validation list with dates to see if the issue persists. This can help determine if the problem is specific to the SharePoint file or if it is a system-wide issue.
  6. Update Excel:
    • Ensure that your Excel application is up to date by installing any available updates from Microsoft.
  7. Verify SharePoint Settings:
    • Check if there are any SharePoint settings or restrictions that could be affecting the way data is displayed or formatted in Excel when accessed through SharePoint.
  8. Compare Excel Add-Ins:
    • Compare the list of Excel add-ins enabled on your computer with those of your team members. There might be an add-in causing the issue.

If none of these steps resolve the problem, consider providing more specific details about your Excel version and SharePoint configuration to get more targeted assistance.

The text and steps were edited with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.