Datevalue excel как пользоваться

Обновлено: 07.07.2024

Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), - преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ("11.09.2009") возвращает число 40067, соответствующее 11 сентября 2009 года. Но, функция ДАТАЗНАЧ() понимает только определенные форматы записи дат. Например, 2009-сент-11 она не поймет, а 11-сент-2009 - поймет.

Если на листе содержатся даты в текстовом формате, то их скорее всего не удастся правильно отфильтровать, отсортировать или отформатировать в виде дат без предварительного преобразования в числовую форму (см. статью Как EXCEL хранит дату и время ).

Функция ДАТАЗНАЧ() предназначена для преобразования в формат дат.

Синтаксис функции ДАТАЗНАЧ()

ДАТАЗНАЧ(дата_как_текст)

Дата_как_текст. Текст, представляющий собой дату в формате даты EXCEL (об этом ниже), или ссылка на ячейку с таким текстом. Например, "11.9.2009" и "11-сент-2009" введенные (без кавычек) в ячейки в текстовом формате.

Если в аргументе дата_как_текст опущен год, функция ДАТАЗНАЧ() использует текущий год по показаниям встроенных часов компьютера. Сведения о времени в аргументе дата_как_текст игнорируются.

Задача 1 Фильтрация и сортировка дат

Предположим, что столбце A установлен формат Текстовый и он содержит текстовые строки "22 сентябрь 2011"; "23 сентябрь 2011" и т.д. (без кавычек).

Постараемся отфильтровать значения, которые относятся к октябрю 2011 года.

Сначала попытаемся отфильтровать даты как есть, т.е. в формате текста.

Так как значения дат находятся в текстовом формате, то и Фильтр в столбце А будет содержать только критерии для текста.

Выбрав в Фильтре критерий Текстовые фильтры / Содержит. введите текст Октябрь и нажмите ОК. Будут отфильтрованы значения относящиеся к октябрю 2011 и 2012 годов (последняя строка).

Кроме того, если была допущена опечатка, то заметить, что отфильтровались не все значения будет очень трудно. Поэтому стоит перед фильтрацией преобразовать значения в текстовый формат. Для этого используем функцию ДАТАЗНАЧ() , записав в столбце В следующую формулу :

К столбцу В применим формат Краткая Дата и затем применим фильтр.

Как видно из рисунка выше, фильтр теперь будет содержать критерии удобные для отбора дат.

Аналогично Фильтрации работает и сортировка списка с датами. Сравните 2 сортировки выполненные для столбца А (даты в текстовом формате) и В (формат даты).

Задача 2 Вычисления с датами в текстовом формате

Для того, чтобы производить вычисления с датами функция ДАТАЗНАЧ() не нужна. Пусть в ячейке А1 хранится текстовое значение "11.9.2009". Например, чтобы прибавить к этой дате 2 дня, можно записать формулу

Форматы дат, которые функция ДАТАЗНАЧ() не понимает

Дату в текстовом формате можно записать разными способами, например "2/8/2009" или "2\8\2009" или "2001, сентября 11". Но функция ДАТАЗНАЧ() (и соответственно EXCEL) поймет далеко не все форматы, понятные человеку (функция первый пример поймет, а второй и третий не поймет). Сводная таблица форматов приведена на рисунке ниже и в файле примера (лист форматы ) .

Кроме того, EXCEL умеет преобразовывать далеко не все форматы дат (записанных в текстовой форме), которые предлагает сам. Например, если дату в ячейке ввести в формате EXCEL, который называется Длинный формат даты (предположим "22 сентября 2011 г."), и попытаться преобразовать его в дату с помощью функции ДАТАЗНАЧ() , то получим ошибку.

Альтернатива функции ДАТАЗНАЧ()

Функция ДАТАЗНАЧ() полезна если имеется длинный перечень дат (например, неудачно импортированных из MS WORD). Если дат в текстовом формате немного, то можно выделить ячейку с датой, нажать клавишу F2 , затем ENTER .

Другой способ - прибавить к значению 0, т.е. формулы =ДАТАЗНАЧ(A1) и =А1+0 эквивалентны!

О преобразовании дат из текстового формата читайте в этой статье .

ДАТАЗНАЧ() vs ДАТА()

Если значение даты содержится в 3-х разных ячейках (в А1 содержится день, в А2 - месяц и А3 - год), то преобразовать это значение можно с помощью функций ДАТАЗНАЧ() и ДАТА() :

=ДАТАЗНАЧ(A1 & "/" & A2 & "/" & A3)

В случае, если месяц в ячейке А2 введен прописью, то функция ДАТА() не справится.

Возвращает значение типа Variant (Date).

DateValue ( дата )

Требуемая датаАргумент обычно является строковое выражение, представляющая дату с 1 января 100 г. по 31 декабря 9999 г. Однако даты могут также быть любыми выражение, которые могут представлять дату, время или дату и время в этом диапазоне.

Если дата — это строка, которая содержит только числа, разделенные разделители даты, DateValue распознает порядок для месяца, дня и года в соответствии с кратким форматом даты, который вы указали для вашей системы. DateValue также распознает многозначные даты, содержащие названия месяцев в длинной или сокращенной форме. Например, Помимо распознания дат 30.12.1991 и 30.12.91, DateValue распознает также 30 декабря 1991 г. и 30 декабря 1991 г.

Если в аргументе дата год опущен, DateValue использует текущий год из системной даты компьютера.

Если аргумент дата включает данные о времени, DateValue не возвращает их. Однако если дата включает недопустимые данные о времени (например, "89:98"), возникает ошибка.

Примечание: Если в свойстве Calendar задан григорианский календарь, аргумент дата следует указывать соответствующим образом. Если используется календарь Хиджра, дата должна соответствовать ему. Если дата указана по календарю Хиджра, аргумент дата является переменной String, представляющей дату с 01.01.100 (2 августа 718 года по григорианскому календарю) по 03.04.9666 (31 декабря 9999 года по григорианскому календарю).

Примеры запросов

SELECT DateValue([DateTime]) AS Expr1 FROM ProductSales;

Возвращает значения даты для поля "Дата и время" в кратком формате даты без значений времени.

SELECT DateValue([DateTime]) AS NewDate, Count(ProductSales.DateTime) AS CountOfDateTime FROM ProductSales GROUP BY DateValue([DateTime]);

Возвращает значения даты для поля "Дата и время" в кратком формате даты без значений времени.

Пример VBA

Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.

This article describes the formula syntax and usage of the DATEVALUE function in Microsoft Excel.

Description

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example

The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations.

To view a date serial number as a date, you must apply a date format to the cell. Find links to more information about displaying numbers as dates in the See Also section.

Syntax

The DATEVALUE function syntax has the following arguments:

Date_text Required. Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer's built-in clock. Time information in the date_text argument is ignored.

Remarks

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

Most functions automatically convert date values to serial numbers.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Функция DATEVALUE - это встроенная функция, которая относится к категории функций DATE / TIME, где она используется для преобразования сохраненных дат в тексте в соответствующий формат даты. Значение CSV с разделением запятыми Формат CSV всегда поставляется в текстовом формате. В этом случае функция DATEVALUE будет очень полезна для преобразования текста в правильный формат даты.

Функция DATEVALUE чаще всего используется в сценарии, когда мы используем его для загрузки файла из онлайн-источников, таких как PDF, CSV или формат Rich Text. В большинстве случаев эти форматы подпадают под только текст в этих случаях, если у нас есть даты, сохраненные в текстовом формате, эта функция DATEVALUE наиболее полезна для преобразования текста в правильный формат данных последовательного интерфейса.

Мы можем найти функцию DATEVALUE в Excel в функции DATE / TIME.

DATEVALUE Формула в Excel

Ниже приведена формула DATEVALUE:

Аргументы DATEVALUE в Excel:

Это DATEVALUE использует нижеуказанные аргументы

date_text: - Этот аргумент представляет текст, который представляет дату в правильном формате даты Excel.

Как открыть функцию DATEVALUE в Excel?

Ниже приведены шаги для открытия функции DATEVALUE в Excel.

Перейти в меню формулы. Нажмите Вставить функцию.

Как только вы выберете функцию ДАТА / ВРЕМЯ, мы получим список функций, а затем выберем функцию ДАТА ЗНАЧЕНИЯ.

В расширенной версии мы можем найти ярлык в меню формул, таких как DATE и Time Function. Здесь мы можем выбрать функцию DATE / TIME категории, и она перечислит набор функций, где мы можем найти функцию DATEVALUE в списке, и это показано на скриншоте ниже.

Как использовать функцию DATEVALUE в Excel?

DATEVALUE Функция очень проста в использовании. Давайте теперь посмотрим, как использовать функцию DATEVALUE в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон Excel функции DATEVALUE здесь - Шаблон Excel функции DATEVALUE

DATEVALUE в Excel - Пример № 1

В этом примере мы видим, что у нас есть два столбца с именами Date Text и DateValue

Где Date Text содержит только текст с месяцем Day и Year.

Теперь давайте используем функцию DATEVALUE для преобразования текста даты в правильный формат даты.

Вставьте функцию DATEVALUE, выбрав соответствующие ячейки = DATEVALUE (A2)

Как только мы нажмем клавишу ввода, будут отображены следующие результаты, где DATEVALUE возвращает строки серийных номеров, чтобы отобразить в формате даты, нам нужно отформатировать ячейки в формате даты.

Чтобы преодолеть это значение ошибки, нам нужно отформатировать ячейки в формате даты

Точно так же мы можем найти другое значение

Теперь Excel обрабатывает вышеуказанную функцию как текст (строки), и ее можно изменить на формат даты, отформатировав ячейки, как показано ниже.

Мы можем отформатировать ячейки, выполнив следующие шаги;

Щелкните правой кнопкой мыши на ячейке. Вы получите вариант формата ячеек, как показано на скриншоте ниже

Нажмите формат ячейки. Мы получим список параметров формата, как показано ниже

Выберите соответствующий параметр даты по умолчанию Excel выберет формат, который был отмечен как *

В расширенной версии у нас есть еще один способ форматирования, где мы можем найти номерную вкладку, как показано на скриншоте ниже

Нажмите на выпадающий список, где вы найдете различные форматы в строке меню, как показано ниже

Теперь выберите дату или короткую дату из списка.

Таким образом, мы можем отформатировать ячейки, чтобы получить точную дату.

DATEVALUE в Excel - Пример № 2

Теперь давайте рассмотрим приведенный ниже пример того, как работает функция datevalue. Здесь, в приведенном ниже примере, мы можем видеть два столбца с именем date, который находится в текстовом формате, поэтому для преобразования в формат даты мы будем использовать функцию DATEVALUE

Шаги, чтобы следовать для функции DATEVALUe

Нажмите «Вставить функцию» на вкладке «Формула» Мы получим диалоговое окно, как показано ниже

Выберите DATE / TIME Category и выберите функцию DATEVALUE, как показано ниже

Как только мы нажмем функцию DATEVALUE, мы получим диалоговое окно ниже.

Выберите ячейку для преобразования в DATEVALUE и нажмите кнопку ОК, как показано на скриншоте ниже

На скриншоте ниже мы видим, что он показывает некоторые значения серийного номера, а также формат даты в правой части, который выделен желтым цветом.

Мы получим приведенный ниже результат в виде серийных номеров, где он обозначает 21 января 2018 года, равен 43121,

Точно так же мы можем найти два других

Здесь мы можем задаться вопросом, почему эти серийные номера появляются, потому что конкретные ячейки имеют общий формат, как показано на скриншоте ниже

Теперь нам нужно изменить этот общий формат на правильный формат даты, выбрав короткий код из выпадающего списка.

После форматирования всех ячеек мы видим, что все серийные номера преобразованы в правильный формат даты, который показан ниже как окончательный результат.