How to correct a #VALUE! error in the DAYS function (2023)

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007More...Less

This topic lists the commonly found causes of the #VALUE! error when working with DAYS.

Problem: The arguments do not refer to a valid date

If your arguments contain dates in the form of text, such asJune 22 2000, Excel cannot interpret the value as a valid date and throws out the #VALUE! error.

Note:A date in a text format such as21-Oct-2006is accepted by the function, and will not result in a #VALUE! error.

Solution: Change the value to a valid date, for example,06/22/2000and test the formula again.

Problem: Your system date and time settings are not in sync with the dates in the cells.

If your system follows the mm/dd/yy date setting and your formula follows a different setting, for example, dd/mm/yyyy, you will receive the #VALUE! error.

Solution: Check your system’s date and time settings to make sure that they match the format of the dates referred to in the formula. If necessary, update the formula to the correct format.

The following example shows different variations of DAYS, where A2 and A7 are invalid dates.

How to correct a #VALUE! error in the DAYS function (1)

Need more help?

You can always ask an expert in theExcel Tech Communityor get support in theAnswers community.

See Also

Correct a #VALUE! error

DAYS function

Calculate the difference between two dates

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Need more help?

Want more options?

Discover Community

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.


How to correct a #VALUE! error in the DAYS function? ›

If your system follows the mm/dd/yy date setting and your formula follows a different setting, for example, dd/mm/yyyy, you will receive the #VALUE! error. Solution: Check your system's date and time settings to make sure that they match the format of the dates referred to in the formula.

How do you fix a value error in the date function? ›

Solution: You have to change to the correct value. Right-click on the cell and click Format Cells (or press CTRL+1) and make sure the cell follows the Text format. If the value already contains text, make sure it follows a correct format, for e.g. 22 June 2000.

How do I get rid of #value error in Excel? ›

To eliminate the #VALUE error in your Excel sheets, you can use the IFERROR function in Excel 2007 - 365 or the IF ISERROR combination in earlier versions. If one or both dates are invalid, the formula results in a #VALUE error.

What is #value error in Excel time function? ›

error occurs in TIMEVALUE is when the time_text argument contains a value other than a text string. The time_text argument has to be a valid text value, not a number or a time value. For example, 22-June-2000 6:45 AM or 22-June-00 are valid values, but the following values are not: June-22-2000 6:45 AM.

How do you fix a value in an Excel equation? ›

Tip: When you are editing a cell that contains a formula, you can press F9 to permanently replace the formula with its calculated value.

How do you handle error in a function? ›

Methods of Error Handling in C
  1. perror() perror() function stands for print error and when called by the user, it displays a message describing about the most recent error that occured in the code. ...
  2. strerror() strerror() function is contained in string. ...
  3. ferror() ...
  4. feof() ...
  5. clearerr() ...
  6. Exit Status. ...
  7. Division by zero.

How do you fix formula errors? ›

Select the worksheet you want to check for errors. If the worksheet is manually calculated, press F9 to recalculate. If the Error Checking dialog is not displayed, then click on the Formulas tab > Formula Auditing > Error Checking button.

How do I replace all error values in Excel? ›

Step 1: Select the range that you will work with. Step 2: Press the F5 key to open the Go To dialog box. Step 3: Click the Special button, and it opens the Go to Special dialog box. Step 6: Now just enter 0 or any other value that you need to replace the errors, and press Ctrl + Enter keys.

What is a value error in Excel DAYS360? ›

The DAYS360 function returns a #VALUE! error, if either the start_date or end_date argument is passed as a text value that can not be interpreted as a date. If the dates entered in either the start_date or end_date argument are out of the Excel's recognized range, the function returns a #NUM! error.

How do I replace #value with text in Excel? ›

Replace one value with another

Select the range of cells where you want to replace text or numbers. To replace character(s) across the entire worksheet, click any cell on the active sheet. Press the Ctrl + H shortcut to open the Replace tab of the Excel Find and Replace dialog.

How do you fix a function in Excel? ›

If you selected cells below, press CTRL+D to fill the formula down. If you selected cells above, click Home > Fill > Up to fill the formula up. If you selected cells to the right, press CTRL+R to fill the formula to the right. If you selected cells to the left, click Home > Fill > Left to fill the formula to the left.

Which function is used to remove error from our formula? ›

Sometimes you just want to replace the #VALUE! error with something else like your own text, a zero or a blank cell. In this case you can add the IFERROR function to your formula. IFERROR will check to see if there's an error, and if so, replace it with another value of your choice.

How do you find the value of an error function? ›

Abstract. The error function erf is a special function. It is widely used in statistical computations for instance, where it is also known as the standard normal cumulative probability. The complementary error function is defined as erfc ( x ) = 1 − erf ( x ) .

What is the error function in Excel? ›

The IsError function is used to determine if a numeric expression represents an error. IsError returns True if the expression argument indicates an error; otherwise, it returns False.

How do I replace #value with 0 in Excel? ›

errors with 0:
  1. Select the cell or range of cells that contains the #VALUE! errors you want to replace with 0.
  2. Press Ctrl+H to open the Find and Replace dialog box.
  3. In the "Find what" field, type "#VALUE!" ( ...
  4. In the "Replace with" field, type "0" (without the quotes).
  5. Click "Replace All" to replace all #VALUE!

How do I replace a value in a text file? ›

Open the text file in Microsoft Word. In the Ribbon menu, on the Home tab, click the Replace option. In the Find and Replace window, enter the text you want to find and the text you want to use as a replacement. See our using search and replace and advanced options for further information and help.

How do I replace a value in a specific column in Excel? ›

To quickly find and replace text, use the Ctrl+F shortcut. This will open the Find and Replace dialog box. Then, simply type the text you want to find and replace in the appropriate fields and click the Replace button. To quickly find and replace text in a specific column, use the Ctrl+Shift+F shortcut.

How do you change values in Excel? ›

Using Keyboard Shortcut
  1. Select the cells for which you want to convert formulas to values.
  2. Copy the cells (Control + C).
  3. Paste as Values – Keyboard Shortcut – ALT + ESV.

How do you fix a fixed text in Excel? ›

Fix data that is cut off in cells
  1. Select the cells.
  2. On the Home tab, click Wrap Text. The text in the selected cell wraps to fit the column width. When you change the column width, text wrapping adjusts automatically. Note: If all wrapped text is not visible, it might be because the row is set to a specific height.

Why is the Datevalue function not working in Excel? ›

Please note that the DATEVALUE function doesn't work when your dates are already in the date format. It will return a #VALUE error instead. Also, the text you are trying to convert should be typed in the Excel date format. Ex: “2/3/2022”, “2-March-2022”, “2-3-2022”, and “23 Sep 2022” are recognized Excel date formats.

How do I remove the error in date in power query? ›

Keep rows with data errors
  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
  2. Decide which columns for which you want to remove errors. ...
  3. Select Remove Errors.

Why is it showing ### in date on Excel? ›

As dates and times are stored as positive numeric values within Excel, some values (for example negative values) are invalid as dates or times. Excel shows this by filling the cell with # symbols.

How to fix date format in SQL? ›

How to get different date formats in SQL Server
  1. Use the SELECT statement with CONVERT function and date format option for the date values needed.
  2. To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
  3. To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
Dec 8, 2022

How to convert Datevalue to date? ›

Convert text dates by using the DATEVALUE function
  1. Enter =DATEVALUE(
  2. Click the cell that contains the text-formatted date that you want to convert.
  3. Enter )
  4. Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date. What is an Excel serial number?

How do I auto fix the date in Excel? ›

Another way is to enter your first date, right-click the fill handle and drag the fill handle through the cells you want to auto fill with dates, and then release it. When you do, Excel displays a context menu and you select the appropriate option from it.

Why is my date formula not updating in Excel? ›

Excel formulas not updating

When Excel formulas are not updating automatically, most likely it's because the Calculation setting has been changed to Manual instead of Automatic. To fix this, just set the Calculation option to Automatic again.

How do you replace a value error in Power Query? ›

To replace rows that have errors, first select the column that contains errors. On the Transform tab, in the Any column group, select Replace values. From the drop-down menu, select Replace errors. In the Replace errors dialog box, enter the value 10 because you want to replace all errors with the value 10.

How do you replace date error with null in Power Query? ›

In Power Query, you can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors”.

How do I correct a date in Power Query? ›

Simple Power Query date format transformations
  1. Click the data type icon in the column header and select Date.
  2. Select the column, then click Transform > Data Type > Date from the Ribbon.
  3. Right-click on the column header, then click Change Type > Date.
  4. Change the applied data type in the M code to type date.
Dec 1, 2022

What is the format of day in SQL? ›

SQL Date Time Format Data Types

The following types of data are available in SQL Server for storing Date or date/time values in the database: DATE - format: YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.

How to convert string date to date? ›

  1. Get the String to be converted.
  2. Create an empty Instant timestamp object.
  3. Convert the String to Date using the Instant. parse() method.
  4. If converted successfully, then print the Date.
  5. If not converted successfully, then DateTimeParseException is thrown.
Mar 1, 2023


Top Articles
Latest Posts
Article information

Author: Rev. Leonie Wyman

Last Updated: 30/07/2023

Views: 5463

Rating: 4.9 / 5 (79 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Rev. Leonie Wyman

Birthday: 1993-07-01

Address: Suite 763 6272 Lang Bypass, New Xochitlport, VT 72704-3308

Phone: +22014484519944

Job: Banking Officer

Hobby: Sailing, Gaming, Basketball, Calligraphy, Mycology, Astronomy, Juggling

Introduction: My name is Rev. Leonie Wyman, I am a colorful, tasty, splendid, fair, witty, gorgeous, splendid person who loves writing and wants to share my knowledge and understanding with you.