Excel ошибка datevalue

Important: When using Date functions in Excel, always remember that the dates you use in your formula are affected by the Date and Time settings on your system. When Excel finds an incompatibility between the format in the date_text argument and the system’s Date and Time settings, you will see a #VALUE! error. So the first thing you will need to check when you encounter the #VALUE! error with a Date function is to verify if your Date and Time settings are supporting the Date format in the date_text argument.

Here are the most common scenarios where the #VALUE! error occurs:

Problem: The date_text argument has an invalid value

The date_text argument has to be a valid text value, and not a number or a date. For example, 22 June 2000 is a valid value, but the following values are not:

• 366699

• 06/22/2000

• 2000 June 22

• June 22 2000

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.

Problem: The value in the date_text argument is not in sync with the system’s date and time settings

If your system date and time settings follow the mm/dd/yyyy format, then a formula such as =DATEVALUE(“22/6/2000”) will result in a #VALUE! error. But the same formula will display the correct value when the system’s date and time is set to dd/mm/yyyy format.

Solution: Make sure that your system’s date and time settings (both Short time and Long time) matches the date format in the date_text argument.

Problem: The date is not between January 1, 1990 and December 31, 9999

Solution: Make sure that the date_text argument represents a date between January 1, 1990 and December 31, 9999.

The following example lists the output of different DATEVALUE functions.

Note: For this example, the Date and Time settings are set to M/d/yyyy and dddd,MMMM d,yyyy for the Short Date and Long Date formats respectively.

Do you have a specific function question?

Post a question in the Excel community forum

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Correct a #VALUE! error

DATEVALUE 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)

DATEVALUE() is designed to make a Date out of plain text. Your cell is currently a Date/Time, which is a numeric value. I recommend using one of the following solutions to get the date from the cell.

Using DATE()

---

This is the cleanest option and the method that I would recommend.

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

YEAR() gets the Year value from the cell, MONTH() gets the Month value, and DAY() gets the Day value. The DATE() function takes a Year, Month, and Day value, so by passing them into DATE() we can get the Date value from A2.

Using INT()

---

If we look at the numeric value of your Date in A2, we see that it is 41841.5309722222. The whole portion of the number (41841.) is the date and the decimal portion (.5309722222) is the time. So if we take INT(A2) to convert this value to an integer, we will lose the decimal portion so that all that remains (41841) is the date. So this is our formula for using INT()

=INT(A2)

The same idea can be accomplished with ROUNDDOWN(A2,0) or =FLOOR.MATH(A2) or =FLOOR(A2,1).

Using DATEVALUE()

---

While the first solution is the cleanest, there is a way to do this with DATEVALUE() that involves converting the cell into Text first. The TEXT() function takes a value and a format string, so we format the cell value as Text as follows

=TEXT(A2,"yyyy-mm-dd")

And this gives us

2014-07-21

We then pass that result into DATEVALUE()

=DATEVALUE(TEXT(A2,"yyyy-mm-dd"))

You will need to format the result as a date.

Using LEFT() and DATEVALUE()

---

Based on this Stackoverflow question that I found, it appears the issue could be a result of inconsistent formatting, so you might try this solution

=DATEVALUE(LEFT(A2,FIND(" ",A2)-1))

I have included my results with this and the other methods in a screenshot below. You can see by my use of the TYPE() command below the value that I tested this on both a number and text.

Results

Formatting

---

I’m assuming you want just the date for calculation purposes, but if you just want to display the date, you can format the cell to only show the date and ignore the time element although the time element will still be present in the cell. I’m assuming you know about this, but since you didn’t specify, it is a possible solution.

In short, Excel’s DateTime epoch is not the same as VBA’s DateTime epoch. Although, they are the same once you get past February 28th, 1900.

From Joel Spolksy’s blog:

In most modern programming environments, dates are stored as real
numbers. The integer part of the number is the number of days since
some agreed-upon date in the past, called the epoch. In Excel, today’s
date, June 16, 2006, is stored as 38884, counting days where January
1st, 1900 is 1.

I started working through the various date and time functions in Basic
and the date and time functions in Excel, trying things out, when I
noticed something strange in the Visual Basic documentation: Basic
uses December 31, 1899 as the epoch instead of January 1, 1900, but
for some reason, today’s date was the same in Excel as it was in
Basic.

Huh?

I went to find an Excel developer who was old enough to remember why.
Ed Fries seemed to know the answer.

«Oh,» he told me. «Check out February 28th, 1900.»

«It’s 59,» I said.

«Now try March 1st.»

«It’s 61!»

«What happened to 60?» Ed asked.

«February 29th. 1900 was a leap year! It’s divisible by 4!»

«Good guess, but no cigar,» Ed said, and left me wondering for a
while.

Oops. I did some research. Years that are divisible by 100 are not
leap years, unless they’re also divisible by 400.

1900 wasn’t a leap year.

«It’s a bug in Excel!» I exclaimed.

«Well, not really,» said Ed. «We had to do it that way because we need
to be able to import Lotus 123 worksheets.»

«So, it’s a bug in Lotus 123?»

«Yeah, but probably an intentional one. Lotus had to fit in 640K.
That’s not a lot of memory. If you ignore 1900, you can figure out if
a given year is a leap year just by looking to see if the rightmost
two bits are zero. That’s really fast and easy. The Lotus guys
probably figured it didn’t matter to be wrong for those two months way
in the past. It looks like the Basic guys wanted to be anal about
those two months, so they moved the epoch one day back.»

«Aargh!» I said, and went off to study why there was a checkbox in the
options dialog called 1904 Date System.

---

The information below was taken from this Super User answer.

---

As described in Microsoft KB 214058:

Days of the week before March 1, 1900 are incorrect in Excel

MORE INFORMATION

When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.

However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 («leap day») in the year 1900, the day of the week for any date before March 1, 1900 (the day after the «leap day»), is not computed correctly.

The «other spreadsheet programs» refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:

Excel 2000 incorrectly assumes that the year 1900 is a leap year

MORE INFORMATION

When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.

When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.

Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.

If this behavior were to be corrected, many problems would arise, including the following:

• Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.
• Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.
• Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.

If the behavior remains uncorrected, only one problem occurs:

• The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.

All your hard work of maintaining data with proper date seems to go wasted because all of a sudden Excel not recognizing date format?

This problematic situation can happen anytime and with anyone so you all need to be prepared for fixing up this Excel date format messes up smartly.

If you don’t have any idea how to fix Excel not recognizing date format issues then also you need not get worried. As in our today’s blog topic, we will discuss this specific date format not changing in Excel problem and ways to fix this.

• When you copy or import data into Excel and all your date formats go wrong.
• Excel recognizes the wrong dates and all the days and months get switched.
• While working with the Excel file which is exported through the report. You may find that after changing the date value cell format into a date is returning the wrong data.

Why Excel Not Recognizing Date Format?

Your system is recognizing dates in the format of dd/mm/yy. Whereas, in your source file or from where you are copying/importing the data, it follows the mm/dd/yy date format.

The second possibility is that while trying to sort that dates column, all the data get arranged in the incorrect order.

How To Fix Excel Not Recognizing Date Format?

Resolving date not changing in Excel issue is not that tough task to do but for that, you need to know the right solution.

Here are fixes that you need to apply for fixing up this problem of Excel not recognizing date format.

1# Text To Columns

Here are the fixes that you need to perform.

• Firstly you need to highlight the cells having the dates. If you want then you select the complete column.
• Now from the Excel ribbon, tap to the Data menu and choose the ‘Text to columns’ option.

• In the opened dialog box choose the option of ‘Fixed width’ and then hit the Next button.

• If there are vertical lines with arrows present which are called column break lines. Then immediately go through the data section and make a double click on these for removing all of these. After that hit the Next button.
• Now in the ‘Column data format’ sections hit the date dropdown and choose the MDY or whatever date format you want to apply.
• Hit the Finish button.

Very soon, your Excel will read out all the imported MDY dates and after then converts it to the DMY format. All your problem of Excel not recognizing date format is now been fixed in just a few seconds.

2# Dates As Text

In the shown figure, you can see there is an imported dates column in which both data and time are showing. If in case you don’t want to show the time and for this, you have set the format of the column as Short Date. But nothing happened date format not changing in Excel.

Now the question arises why Excel date format is not changing?

 Mainly this happens because Excel is considering these dates as text. However, Excel won’t apply number formatting in the text.

Having doubt whether your date cell content is also counted as text then check out these signs to clear your doubt.

• If it is counted as text then Dates will appear left-aligned.
• You will see an apostrophe in the very beginning of the date.
• When you select more than one dates the Quick Calc which present in the Status Bar will only show you the Count, not the Numerical sum or Count.

3# Convert Date Into Numbers

Once you identify that your date is a text format, it’s time to change the date into numbers to resolve the issue. That’s how Excel will store only the valid dates.

For such a task, the best solution is to use the Text to Columns feature of Excel. Here is how it is to be used.

• Choose the cells first in which your dates are present.
• From the Excel Ribbon, tap the Data.
• Hit the Text to Columns option.

• In the first step, you need to choose the Delimited option, and click the Next.

• In the second step, from the delimiter section chooses Space In the below-shown preview pane you can see the dates divided into columns.
• Click the Next.

In the third step set the data type of each column:

• Now in the preview pane, hit the date column and choose the Date option.
• From the Date drop-down, you have to select the date format in which your dates will be displayed.

Suppose, the dates are appearing in month/day/year format then choose the MDY.

• After that for each remaining column, you have to choose the option “Do not import column (skip)”.
• Tap the Finish option for converting the text format dates into real dates.

4# Format The Dates

After the conversion of the text dates or real dates, it’s time to format them using the Number Format commands.

Here are a few signs that help you to recognize the real dates easily:

• If it is counted as text then Dates will appear right-aligned.
• You will see no apostrophe at the very beginning of the date.
• When you select more than one date the Quick Calc which present in the Status Bar will only show you the Count, Numerical count, and the sum.

For the formatting of the dates, from the Excel ribbon choose the Number formats.

Everything will work smoothly now, after converting text to real dates.

5# Use The VALUE Function

Here is the Syntax which you need to use:

=VALUE (text) 

Where ‘text’ is referencing the cell having the date text string.

The VALUE function is mainly used for compatibility with other workbook programs. Through this, you can easily convert the text string which looks like a number into a real number.

 Overall it’s helpful for fixing any type of number not only the dates.

Notes:

1. If the text string which you are using is not in the format that is well recognized by Excel then it will surely give you the #VALUE! Error.
2. For your date Excel will return the serial numbers, so you need to set the cell format as the date for making the serial number appear just like a date.

6# DATEVALUE Function

Here is the Syntax which you need to use:

=DATEVALUE(date_text)

Where ‘date_text’ is referencing the cell having the date text string.

DATEVALUE is very much similar to the VALUE function. The only difference is that it can convert the text string which appears like a date into the date serial number.

With the VALUE function, you can also convert a cell having the date and time which is formatted as text. Whereas, the DATEVALUE function, ignores the time section of the text string.

7#  Find & Replace

In your dates, if the decimal is used as delimiters like this, 1.01.2014 then the DATEVALUE and VALUE are of no use

In such cases using Excel Find & Replace is the best option. Using the Find and Replace you can easily replace decimal with the forward slash. This will convert the text string into an Excel serial number.

Here are the steps to use find & replace:

1. Choose the dates in which you are getting the Excel not recognizing date format issue.
2. From your keyboard press CTRL+H This will open the find and replace dialog box on your screen.
3. Now in the ‘Find what’ field put a decimal, and in the ‘replace’ field put a forward slash.
4. Tap the ‘Replace All’ option.

Excel can now easily identify that your text is in number and thus automatically format it like a date.

HELPFUL ARTICLE: 6 Ways To Fix Excel Find And Replace Not Working Issue

8# Error Checking

Last but not least option which is left to fix Excel not recognizing date format is using the inbuilt error checking the option of Excel.

This inbuilt tool always looks for the numbers formatted as text.

You can easily identify if there is an error, as it put a small green triangle sign in the top left corner of the cell. When you select this cell you will see an exclamation mark across it.

By hitting this exclamation mark you will get some more options related to your text:

In the above-shown example, the year in the date is only showing 2 digits. Thus Excel is asking in what format I need to convert the 19XX or 20XX.

You can easily fix all the listed dates with this error checking option. For this, you need to choose all your cells having date text strings right before hitting the Exclamation mark on your first selected cell.

Steps to turn on error checking option:

• Hit the office icon present in the top left corner of the opened Excel workbook. Now go to the Excel Options > Formulas
• From the error checking section choose “enable background error checking” option.
• Now from the error checking rules choose the “cells containing years represented as 2 digits” and “number formatted as text or preceded by an apostrophe”.

Wrap Up:

When you import data to Excel from any other program then the chances are high that the dates will be in text format. This means you can’t use it much in formulas or PivotTables.

There are several ways to fix Excel not recognizing date format. The method you select will partly depend on its format and your preferences for non-formula and formula solutions.

So good luck with the fixes…!

Вопрос:

Когда я использую формулу datevalue ( “01/01/1900” ), я получаю 1 и отформатирован как дата, которую он показывает как 01/01/1900

Когда я использую VBA

.Range("A1").Value = DateValue("01/01/1900")

он отображается как “02/01/1900” в ячейке

Как это возможно?

Если я использую, например,

.Range("A1").Value = DateValue("01/01/1901")

он отлично работает!

Голова расплавлена ​​!!!

Microsoft заявляет, что – “Используя систему дат по умолчанию в Microsoft Excel для Windows, аргумент date_text должен представлять дату между 1 января 1900 г. и 31 декабря 9999 г.

Лучший ответ:

Короче говоря, Excel DateTime эпоха не совпадает с эпохой VBA DateTime. Хотя, они такие же, как только вы получите 28 февраля 1900 года.

Из Блог Joel Spolksy:

В большинстве современных программных сред даты сохраняются как реальные номера. Целочисленная часть числа – это количество дней с тех пор некоторая согласованная дата в прошлом, называемая эпохой. В Excel сегодня дата, 16 июня 2006 г., хранится как 38884, считая дни, когда январь 1, 1900 – 1.

Я начал работать через различные функции даты и времени в Basic и функции даты и времени в Excel, пытаясь разобраться, когда я заметил что-то странное в документации Visual Basic: Basic использует 31 декабря 1899 года как эпоху вместо 1 января 1900 года, но по какой-то причине, сегодня дата была такой же в Excel, как и в Basic.

А?

Я пошел искать разработчика Excel, который был достаточно взрослым, чтобы помнить, почему. Эд Фрис, казалось, знал ответ.

“О, – сказал он мне.” Отъезд 28 февраля 1900 года “.

” Это 59, – сказал я.

“Теперь попробуйте 1 марта”.

“Это 61!”

“Что случилось с 60?” – спросил Эд.

“29 февраля 1900 года был високосным годом! Он делится на 4!

” Хорошая догадка, но нет сигары “, – сказал Эд, и оставил меня в недоумении в то время.

К сожалению. Я провел некоторое исследование. Годы, которые делятся на 100, не являются високосные годы, если они также не делятся на 400.

1900 не был високосным годом.

” Это ошибка в Excel! “Я воскликнул.

” Ну, не совсем, – сказал Эд. “Мы должны были сделать это так, потому что нам нужно чтобы иметь возможность импортировать рабочие листы Lotus 123”.

“Итак, это ошибка в Lotus 123?”

“Да, но, вероятно, преднамеренный. Лотос должен был вписаться в 640K. Это не так много памяти. Если вы проигнорируете 1900, вы можете выяснить, если данный год – високосный год, просто глядя, чтобы увидеть, два бита равны нулю. Это очень быстро и просто. Лотос вероятно, полагал, что это не имеет значения, чтобы ошибаться в течение этих двух месяцев в прошлом. Похоже, что базовые парни хотели быть аналом эти два месяца, поэтому они вернули эпоху в один прекрасный день”.

“Aargh!” Я сказал, и ушел, чтобы изучить, почему в диалог параметров с именем 1904 Date System.

---

Информация ниже была взята из этого ответа Суперпользователя.

---

Как описано в Microsoft KB 214058:

Содержание

1. Дни недели до 1 марта 1900 года неверны в Excel
2. ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ
3. Excel 2000 неправильно предполагает, что 1900 год является високосным годом
4. ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

Дни недели до 1 марта 1900 года неверны в Excel

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

Когда система даты в Microsoft Excel была первоначально создана, она была разработана для полной совместимости с системами дат, используемыми другими программами для работы с электронными таблицами.

Однако в этой системе даты 1900 год неправильно интерпретируется как високосный год. Поскольку в 1900 году 29 февраля ( “високосный день” ), день недели для любой даты до 1 марта 1900 года (на следующий день после “високосного дня” ) не вычисляется правильно.

“Другие программы электронных таблиц” относятся к Lotus 1-2-3, который был довольно популярен в то время и неправильно принимался в этом году 1900 год был високосным. Это более подробно объясняется в KB 214326:

Excel 2000 неправильно предполагает, что 1900 год является високосным годом

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

Когда Lotus 1-2-3 был впервые выпущен, программа предполагала, что 1900 год был високосным годом, хотя на самом деле это был не високосный год. Это помогло программе справиться с летальными годами и не повредило почти всем вычислениям дат в Lotus 1-2-3.

Когда Microsoft Multiplan и Microsoft Excel были выпущены, они также предположили, что 1900 год был високосным. Это допущение позволило Microsoft Multiplan и Microsoft Excel использовать одну и ту же систему серийных дат, используемую Lotus 1-2-3, и обеспечить большую совместимость с Lotus 1-2-3. Обработка 1900 года как високосного года также облегчила пользователям перемещение листов из одной программы в другую.

Хотя технически возможно исправить это поведение, так что текущие версии Microsoft Excel не предполагают, что 1900 год является високосным годом, недостатки в этом превышают преимущества.

Если это поведение должно быть исправлено, возникнет много проблем, в том числе следующее:

• Почти все даты в текущих листах Microsoft Excel и других документах будут уменьшены на один день. Исправление этого сдвига потребует значительных усилий и времени, особенно в формулах, которые используют даты.
• Некоторые функции, такие как функция WEEKDAY, будут возвращать разные значения; это может привести к неправильной работе формул в рабочих листах.
• Исправление этого поведения приведет к нарушению совместимости даты и времени между Microsoft Excel и другими программами, использующими даты.

Если поведение остается некорректным, возникает только одна проблема:

• Функция WEEKDAY возвращает неправильные значения для дат до 1 марта 1900 года. Поскольку большинство пользователей не используют даты до 1 марта 1900 года, эта проблема встречается редко.