Datevalue excel как пользоваться
Единственная задача функции ДАТАЗНАЧ() , английский вариант 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, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.
Функция DATEVALUE (ДАТАЗНАЧ) в Excel используется для тех ситуаций, когда дата в ячейке указана в виде текста. С её помощью Excel конвертирует текстовое значение даты в число, которое система распознает как дату.
Какие данные отображает
Функция возвращает дату в виде числового значения, понятного по формату для Excel. Например, если мы используем функцию =DATEVALUE («1/1/2016») или =ДАТАЗНАЧ(«1/1/2016») , она будет возвращать число 42370, которое является числовым значением даты 01-01-2016.
Синтаксис
=DATEVALUE (“date_text”) — английская версия
=ДАТАЗНАЧ («дата_как_текст») — русская версия
Аргументы
date_text (дата_как_текст): это аргумент даты указанной в ячейке в формате текста или результат какой-либо формулы в виде текста;
- для правильной работы функции, текст, который нам необходимо преобразовать в формулу важно указать в кавычках (прим. “01/01/2016”);
- если date_text это результат какого-либо вычисления, важно чтобы ячейка была в текстовом формате, для корректной работы функции.
Дополнительная информация
DATEVALUE (ДАТАЗНАЧ) возвращает числовое значение даты:
- Например: формула =DATEVALUE(“1/1/2014”) или =ДАТАЗНАЧ(“1/1/2014”) преобразует значение в число 41640, которое означает дату 1 января 2014 в формате даты.
- Если вы указываете дату без дня (прим. =DATEVALUE(“1/2014”) или =ДАТАЗНАЧ(“1/1/2014”) , а только месяц и год, система вернет числовое значение даты как первого дня указанного месяца — 41640, что для Excel будет означать как 01.01.2014.
- Если вы не используете значение года (прим. =DATEVALUE(“01/04”) или =ДАТАЗНАЧ(“01/04”) , Excel автоматически присвоит текущий год (указанный в настройках даты и времени вашей ОС).
- Формат значения даты, которую выдает функция, зависит от настроек вашей ОС. Например в России (по умолчанию) при преобразовании даты 01/03/2016 это будет 1 Марта 2016, в США это 3 Января 2016.
Примеры использования функции DATEVALUE (ДАТАЗНАЧ)
Пример №1. Преобразование даты в текстовом формате в число
Обратите внимание, что значение, возвращаемое функцией зависит от настройки часов вашей системы. Если дата указана в формате ДД-ММ-ГГГГ значит «01-06-2016» будет отображать как 1 июня 2016 года, но для формата даты ММ-ДД-ГГГГ дата будет отображаться как 6 января 2016.
Пример №2. Преобразование даты в текстовом формате с помощью формулы
Когда дата введена как текст в ячейку, вы можете использовать функцию DATAVALUE (ДАТАЗНАЧ) , чтобы получить числовое значение даты. Обратите внимание, что дата должна быть в текстовом формате.
В приведенном выше примере, формула преобразует “1-6-16” и “1-июня-16” — это значение указанное в формате даты.
Эта функция полезна, когда у вас есть дата в текстовом формате, по которой вы хотите фильтровать, сортировать данные в таблице. В текстовом формате, вы не сможете сортировать даты и проводить вычисления в Excel, так как система будет рассматривать его как текст, а не число.
Пример №3. Использование функции без указания года
Если значение года не указано, то система использует текущий год при отображении значения даты (текущий год 2016 в указанном примере).
Пример №4. Использование функции без указания дня
Если в дате не указан день, то формула использует первый день месяца.
В приведенных выше примерах, когда мы вводим число «6-16», система возвращает число 42522, которое является значением даты на 1 июня 2016 года.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. Less
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.
Excel DATEDVALUE функция
Обычно, когда вы импортируете некоторые данные на лист Excel из других приложений, таких как PDF, TXT файл, дата сохраняется в текстовом формате. В этом случае функция ЗНАЧЕНИЕ ДАТЫ может помочь вам преобразовать дату, сохраненную в текстовом формате, в серийный номер, который Excel может распознать как дату, а затем отформатировать номер в соответствующий формат даты.
Синтаксис:
Синтаксис для DATEVALUE функция в Excel:
Аргументы:
- Date_text : Дата, хранящаяся в виде текстовой строки.
Вернуть:
Вернуть серийный номер на основе определенной даты в Excel.
Применение:
Чтобы преобразовать формат даты текстовой строки в формат реальной даты, примените эту формулу:
Затем отобразятся пятизначные числа, и затем вы должны отформатировать эти серийные номера как обычный формат даты, выбрав Короткое свидание из файла Формат номера раскрывающийся список под Главная вкладку, см. снимок экрана:
А затем перейдите к Формат ячеек диалоговое окно, чтобы указать формат даты и времени для преобразования серийных номеров в формат даты и времени, как показано на следующем снимке экрана:
Внимание: В приведенных выше формулах B2 содержит ли ячейка текстовую строку даты, которую вы хотите преобразовать.
Советы: Вы также можете заменить ссылку на ячейку в приведенных выше формулах текстовой строкой даты, например:
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: