Функция DATEVALUE (ДАТАЗНАЧ) в Excel. Как употреблять

Функция DATEVALUE (ДАТАЗНАЧ) в Excel. Как употреблять?

Функция 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. Преобразование даты в текстовом формате в число

В приведенном выше примере, формула возвращает числовое значение даты введенной в кавычках. Введенная дата обязана быть в текстовом формате, по другому Excel возвращает “#VALUE!” ошибку.

Направьте внимание, что значение, возвращаемое функцией зависит от опции часов вашей системы. Если дата указана в формате ДД-ММ-ГГГГ означает “01-06-2016” будет показывать как 1 июня 2016 года, но для формата даты ММ-ДД-ГГГГ дата будет отображаться как 6 января 2016.

Пример №2. Преобразование даты в текстовом формате при помощи формулы

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

В приведенном выше примере, формула конвертирует “1-6-16” и “1-июня-16” – это значение обозначенное в формате даты.

Эта функция полезна, когда у вас есть дата в текстовом формате, по которой вы желаете фильтровать, сортировать данные в таблице. В текстовом формате, вы не можете сортировать даты и проводить вычисления в Excel, потому что система будет разглядывать его как текст, а не число.

Пример №3. Внедрение функции без указания года

Если значение года не обозначено, то система употребляет текущий год при отображении значения даты (текущий год 2016 в обозначенном примере).

Пример №4. Внедрение функции без указания денька

Если в дате не указан денек, то формула употребляет 1-ый денек месяца.

В приведенных выше примерах, когда мы вводим число “6-16”, система возвращает число 42522, которое является значением даты на 1 июня 2016 года.

Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!

Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Функция ДАТАЗНАЧ в Excel создана для работы с текстовыми данными в формате ДАТА. Она употребляется для преобразования текстовых данных в формат Дата и возвращает числовое значение, характеризующее обозначенную дату.

Как конвертировать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответственное числовому представлению даты, которая указана в виде текста, с учетом обозначенной выше индивидуальности хранения дат в Excel. Формат возвращаемого значения зависит от опций формата ячейки, в которой будет выведен итог вычислений.

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

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строчки, при всем этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

Вид таблицы данных:

Для получения даты в формате, поддерживаемом Excel, используем последующую функцию:

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

1. Функция ЛЕВСИМВ возвращает номер денька (1-ые два знака строчки, содержащейся в ячейке A2). Весьма принципиально, чтоб конкретные номера дней (к примеру, 8 апреля) записывались как 08 апреля (имели нуль в начале), по другому будет возникать ошибка.
2. Композиция функций ПСТР и ЛЕВСИМВ выделяет из строчки три первых знака наименования месяца и возвращает их.
3. Композиция функций ПСТР и ПРАВСИМВ выделяет 4 знака, надлежащие числовому представлению года.

Растянем формулу вниз по столбцу, чтоб высчитать другие значения:

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

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неправильного формата (заместо записи вид «13.06.2019» употребляется 13_06_2019). Такие данные указаны в 2-ух столбцах. В примыкающем нужно вычислить разницу дней меж обозначенными датами.

Вид таблицы данных:

Для расчетов используем последующую формулу:

Для получения текстовой строчки, которая быть может преобразована в данные формата Дата при помощи функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая делает подмену знаков «_» на «.». Итог вычитания 2-ух приобретенных дат – разыскиваемое значение.

Растянем формулу вниз по столбцу чтоб высчитать все значения:

Индивидуальности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет последующую синтаксическую запись:

Единственным аргументом (обязателен для наполнения) является дата_как_текст – текстовое представление даты, которое быть может преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Хоть какой из этих вариантов записи быть может применен в качестве аргумента функции ДАТАЗНАЧ.

Преобразовать строку в дату в excel

В неких вариантах вы сможете впрямую вводить даты в виде числовой строчки для экономии времени и увеличения эффективности работы, но для формальных случаев для вас нужно конвертировать эти числовые строчки в обычное форматирование даты, как показано ниже. Тут я расскажу о неких приемах, которые посодействуют конвертировать числовую строку в обычное форматирование даты в Excel.
Преобразование числовой строчки 20110312 в формат даты с текстом в столбец
Преобразование числовой строчки 40614.43 в дату и время при помощи ячеек формата
Преобразование числовой строчки в обычную дату при помощи Kutools for Excel

Преобразование числовой строчки 20110312 в формат даты с текстом в столбец

Чтоб конвертировать числовую строку, к примеру 20110312, в формат даты, вы сможете применить текст к столбцу для обработки.

1. Изберите числовые строчки, которые необходимо конвертировать в даты, и нажмите Данные > Текст в столбцы. Смотрите снимок экрана:

2. в Текст в столбец диалоговое окно, нажмите Next > Next , чтоб перейти к Шаг 3 из 3, а потом проверьте Времяи изберите YMD из выпадающего перечня справа (если ваша числовая строчка введена с остальным типом даты, вы сможете избрать остальные). Смотрите снимок экрана:

3. Нажмите Окончить, и сейчас числовые строчки преобразованы в даты.

Преобразование числовой строчки 40614.43 в дату и время при помощи ячеек формата

Чтоб конвертировать числовую строку, к примеру 40614.43, в форматирование даты и времени, вы сможете применить функцию «Формат ячеек».

1. Изберите числовую строку, которую желаете конвертировать в дату и время, и нажмите Ctrl + 1 , чтоб открыть Формат ячеек диалог, потом перейдите к Число вкладка, изберите На заказ в Категория перечень, потом введите мм / дд / гггг чч: мм в Тип текстовое окно. Смотрите снимок экрана:

2. Нажмите OK, и все числовые строчки были преобразованы в формат даты и времени.

Преобразование числовой строчки в обычную дату при помощи Kutools for Excel

ЕСЛИ требуется несколько необычных дат для преобразования в обычное форматирование даты, вы сможете применить Kutools for ExcelСоздателя Преобразовать в дату утилита, которая по мере необходимости может стремительно конвертировать разные неординарные даты в обычные.

Опосля бесплатная установка Kutools for Excel, сделайте последующее:

Изберите строчки дат либо чисел, которые необходимо конвертировать в обычные даты, и нажмите Kutools > Content > Преобразовать в дату. Смотрите снимок экрана:

Потом вы сможете узреть, что избранные даты были преобразованы в обычные даты. Смотрите снимок экрана:

Строчка VBA на дату | Преобразование строковых значений в дату в Excel VBA

В Vba есть способ, при помощи которого мы можем конвертировать заданную строку в дату, и этот способ известен как функция CDATE в vba, это интегрированная функция в VBA, и части, нужные для данной для нас функции, должны поначалу конвертировать строку в число, а потом мы преобразуем данное число в дату. Формат результата зависит лишь от формата системной даты.

Одна из всераспространенных заморочек, с которыми мы все сталкиваемся при работе с Excel, — это «Дата и время», которые нередко сохраняются в виде текстовых значений и вначале остаются незамеченными. Но когда им будет нужно употреблять это время, мы узнаем, что эти значения хранятся в виде текста, и мы совершенно не знаем, что с ними созодать. «Дата и время» — это две объединенные вещи в одном элементе, но опосля того, как эти значения сохраняются в виде текстовых значений, работать с ними становится трудно.

Как конвертировать строковые значения в дату?

Пример # 1

Опосля объявления переменной VBA и присвоения ей значения String все, что присвоено данной для нас переменной, будет рассматриваться лишь как строчка. Для примера поглядите приведенный ниже код.

Код:

В приведенном выше коде переменная «k» определена как тип данных «String», и для данной для нас переменной мы присвоили значение «10-21».

Отлично, давайте запустим код и поглядим, что мы получим в окне сообщения в VBA.

Мы получили лишь значение 10-21, но обычно эти значения представляют собой дату, а не строковые значения. Таковым образом, даже невзирая на то, что назначенным типом данных является «Строчка», мы все равно можем конвертировать данные на текущий момент, используя функцию преобразования типа данных CDATE VBA.

Код:

В приведенном выше примере, до этого чем мы покажем итог переменной «k» в окне сообщения, мы назначили функцию CDATE. Изготовлена маленькая корректировка, давайте поглядим, как огромное воздействие это окажет.

Сейчас мы увидим итог как «Дата», а не как «Строку».

Пример # 2

Сейчас поглядите на приведенный ниже код для примера.

Код:

В этот момент приведенный выше код покажет итог как «43599», как мы назначили выше.

Но как мы используем функцию CDATE, она преобразуется в значение даты.

Код:

Итог опосля внедрения функции CDATE будет последующим.

Так как в Excel дата хранится в виде серийных номеров, присвоенный нами серийный номер 43599 равен дате 14.05.2019, когда применяется формат даты.

Мы также можем применить к дате формат «ДД-МММ-ГГГГ» для четкого чтения даты.

Код:

Выше я объявил одну доп переменную для хранения результата. Для данной для нас переменной я применил функцию преобразования CDATE.

Потом я употреблял функцию FORMAT для внедрения формата «ДД-МММ-ГГГГ», и итог будет таковым, как показано ниже.

Благодаря этому мы можем верно прочесть часть денька и месяц. Это также зависит от формата вашей системной даты в Excel, так как мой формат системной даты был «ММ-ДД-ГГГГ», он отображался таковым образом, но это не обязано быть препятствием для форматирования.

Пример # 3

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

В столбце A от A2 до A12 у нас есть значения для поиска по дате, но когда мы смотрим на вкладку формата, он указывает формат «Текст». Сейчас нам необходимо конвертировать эти значения из текста в дату.

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