How to fix the date #VALUE error in Excel

When working with formulas, time-related calculation functions such as DAY function, YEAR function in Excel, many people get the #VALUE error, not the function result. Although you have rechecked the formula, when you re-enter the result, you still report the same error. In some cases, errors with only a few ranges of dates, other sequences still produce the correct results. So what is the way to fix the #VALUE error when executing the time function in Excel?

1. 7 most common Excel errors for office people
2. Fix "File Error: data may have been lost" error in Excel
3. How to fix Errors were detected while saving files in Excel 2010

Instructions for fixing #VALUE error time Excel functions

We will have an example table below. When the function takes a day, some time will report the #VALUE error, some time will result. This error is because the date format in the computer with Excel is heterogeneous , leading to this situation. So you need to change the date format on the system.

How to fix the date #VALUE error in Excel Picture 1

Step 1:

First you access the Control Panel and then click on the Clock, Language, and Region section .

How to fix the date #VALUE error in Excel Picture 2

Switch to the new section Region section click on Change date, time, or number formats .

How to fix the date #VALUE error in Excel Picture 3

Step 2:

A change of date format table for the computer appears. In the Language preferences section of Short date and Long date users adjust the display type as shown.

Finally click Apply to save the changes.

How to fix the date #VALUE error in Excel Picture 4

Note that for Windows 8 or later computers , you will perform differently. First click on the date below the screen and then click on Change date and time settings . in the time frame.

How to fix the date #VALUE error in Excel Picture 5

The Date and time dialog box appears, click on Change date and time .

How to fix the date #VALUE error in Excel Picture 6

Another interface appears, click Next Change calendar settings .

How to fix the date #VALUE error in Excel Picture 7

The Customize Format window appears, select the Date tab and revise the parameters Short date and Long date as well as above.

How to fix the date #VALUE error in Excel Picture 8

Step 3:

Going back to Excel and re-entering the time and executingthe date functions, will see the exact results, not the same error as before.

How to fix the date #VALUE error in Excel Picture 9

Just change the date format on your computer to match the date you entered in Excel to fix the #VALUE error when executing formulas and time-calculating functions in Excel. Depending on the version of Windows in use, we set the date to match Excel.

See more:

1. These are the most basic functions in Excel that you need to understand
2. Instructions on how to fix reverse dates in Excel
3. Instructions on how to insert the current date in Excel

I wish you all success!