HomeApplicationOffice information

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!

3.9 ★ | 63 Vote

You should read it

May be interested

  • Date functions in Excel, DAY, WEEKDAY, MONTHDate functions in Excel, DAY, WEEKDAY, MONTH
    do you want to automatically update the current date, separate years or calculate the distance between two time points? the article below, free download will guide you how to do this professionally with date functions in excel.
  • How to automatically update the Excel file editing timeHow to automatically update the Excel file editing time
    when inserting and editing time excel file, you will control the latest time to edit the file if shared with many people.
  • Calculation of age from date of birth in ExcelCalculation of age from date of birth in Excel
    the following article will guide you how to calculate the age from date of birth in excel so you can calculate the exact number of standard age in years, months and days. in the process of calculating age based on date of birth, the following cases occur: - case 1: calculates the usual age
  • How to fix reverse date errors in ExcelHow to fix reverse date errors in Excel
    the dates are reversed in excel, the month before the next day makes vietnamese users feel strange and unfamiliar. strictly speaking, this is not an error but due to the different ways of writing foreign and vietnamese dates. to correct this you can follow the instructions below of tipsmake.vn.
  • How to convert a sequence of numbers into dates in ExcelHow to convert a sequence of numbers into dates in Excel
    on excel, we can convert the sequence of numbers into dates without manually entering data.
  • Excel date function - Usage and examplesExcel date function - Usage and examples
    the date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
  • How to format dates in Excel quickly and easilyHow to format dates in Excel quickly and easily
    instructions for setting date format in excel 2019, 2016, 2013, 2010... in the article below, you can refer to it to shorten the time when entering date data used in excel.
  • Date time functions in ExcelDate time functions in Excel
    excel supports you to process and calculate quickly with the functions that excel provides such as calculation functions, date functions ... one of them is the function of time to help you handle the prices. time value: hour, minute, second conveniently
  • How to get the current date in ExcelHow to get the current date in Excel
    the following article summarizes the common ways to get the current date in excel fastest.
  • How to use functions to identify and calculate date information in ExcelHow to use functions to identify and calculate date information in Excel
    the following article details how to use functions in excel 2013 and 2016 to identify and calculate date information. to use the functions related to date information, let's explore the following: 1. the relationship between excel and dates is nothing special. first.

Office information