Date format set up on my computer, in relation to Excel

Question

Any formula that uses a date format cell does not work for me, it returns the error "#VALUE".
For my colleague it works, using the same file. I so I think it must we something with my date settings on either Excel or my computer. Anyone knows were this setting is? And what to change to?

Answer 1

If you're encountering a #VALUE! error in Excel when using date format cells, it may indeed be related to the date settings on your computer or within Excel itself. Here are a few steps you can take to troubleshoot and potentially resolve the issue:

1. Check Date Format in Excel: Ensure that the cells containing dates are formatted correctly. You can do this by selecting the cells, right-clicking, and choosing Format Cells. Make sure they are set to a date format that matches how you are entering the dates.
2. Regional Settings: Verify your computer's regional settings. Excel uses these settings to interpret date formats. Go to the Control Panel > Region and Language settings and check that the formats match what you expect (e.g., MM/DD/YYYY vs. DD/MM/YYYY).
3. Text to Columns: If the dates are not recognized correctly, you can convert them to a proper date format using the Text to Columns feature:
   • Select the column with the dates.
   • Go to the Data tab and select Text to Columns.
   • Choose Delimited and click Next.
   • Click Finish after selecting the appropriate delimiters.
   • This will help Excel recognize the dates correctly.
4. Re-enter Dates: Sometimes, re-entering the dates after formatting the cells correctly can resolve the issue.
5. Excel Updates: Ensure that your version of Excel is up to date, as updates can fix bugs related to date handling.

These steps should help you identify and correct any issues with date formatting that could be causing the #VALUE! error in your formulas.

---

References:

• How to control and understand settings in the Format Cells dialog box in Excel
• How Excel works with two-digit year numbers

Answer 2

Hello @Linda Schwartz

Welcome to Q&A forum!

In this case, when your Excel formulas involving dates return #VALUE!, it usually means Excel cannot interpret the date in your cell because of a mismatch between:

• Your system’s regional date format (e.g., MM/DD/YYYY vs DD/MM/YYYY)
• Excel’s date parsing settings

Your colleague’s computer possibly uses a different regional setting that matches the date format in the file.

You can check these two things between you and your friend to see if there is a difference (I will try to give you the image in the Excel directly so you can check it by yourself quickly)

1/ Check your system’s regional date format

• Go to Settings > Time & Language > Region
• Ensure the Short date matches the format used in your Excel file (e.g., MM/DD/YYYY or DD/MM/YYYY).

2/ Check Excel’s date settings

• In Excel, go to: File > Options > Language
• Ensure the language matches your regional settings.

Even though I’ve tried to make this as close as possible to your experience, we may be in different testing environments, and I might not fully understand your needs. Please feel free to contact me again if you still have any concerns.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in "our documentation" to enable e-mail notifications if you want to receive the related email notification for this thread.