Данные, полученные из разных источников, могут содержать значения даты. В этой главе вы поймете, как подготовить данные, содержащие значения данных, для анализа.
Вы узнаете о —
- Форматы даты
- Дата в последовательном формате
- Дата в разных форматах месяц-день-год
- Преобразование дат в последовательном формате в формат месяц-день-год
- Преобразование дат в формате месяц-день-год в последовательный формат
- Получение сегодняшней даты
- Поиск рабочего дня после указанных дней
- Настройка определения выходного дня
- Количество рабочих дней между двумя указанными датами
- Извлечение года, месяца, дня от даты
- Извлечение Дня Недели от Даты
- Получение даты от года, месяца и дня
- Расчет количества лет, месяцев и дней между двумя датами
Форматы даты
Excel поддерживает значения даты двумя способами —
- Серийный формат
- В разных форматах год-месяц-день
Вы можете конвертировать —
-
Дата в последовательном формате до даты в формате год-месяц-день
-
Дата в формате год-месяц-день до даты в последовательном формате
Дата в последовательном формате до даты в формате год-месяц-день
Дата в формате год-месяц-день до даты в последовательном формате
Дата в последовательном формате
Дата в последовательном формате представляет собой положительное целое число, которое представляет количество дней между данной датой и 1 января 1900 года. И текущая Дата, и 1 января 1900 года включаются в число. Например, 42354 является датой, которая представляет 16.12.2015.
Дата в формате месяц-день-год
Excel поддерживает различные форматы даты, основанные на выбранной локали (местоположение). Следовательно, вам необходимо сначала определить совместимость ваших форматов дат и имеющегося анализа данных. Обратите внимание, что определенные форматы дат имеют префикс * (звездочка) —
-
Форматы даты , начинающиеся с * (звездочка), реагируют на изменения региональных настроек даты и времени, указанных для операционной системы.
-
Форматы даты без * (звездочка) не зависят от настроек операционной системы
Форматы даты , начинающиеся с * (звездочка), реагируют на изменения региональных настроек даты и времени, указанных для операционной системы.
Форматы даты без * (звездочка) не зависят от настроек операционной системы
Для понимания цели, вы можете принять Соединенные Штаты в качестве локали. Вы найдете следующие форматы даты для даты — 8 июня 2016 года —
- * 6/8/2016 (зависит от настроек операционной системы)
- * Среда, 8 июня 2016 г. (зависит от настроек операционной системы)
- 6/8
- 6/8/16
- 06/08/16
- 8-июнь
- 8-Июнь-16
- 08-Jun-16
- Июнь-16
- Июнь-16
- J
- J-16
- 6/8/2016
- 8-Июнь-2016
Если вы вводите только две цифры для представления года, и если —
-
Цифры 30 или выше, Excel предполагает, что цифры представляют годы в двадцатом веке.
-
Цифры меньше 30, Excel предполагает, что цифры представляют годы в двадцать первом веке.
Цифры 30 или выше, Excel предполагает, что цифры представляют годы в двадцатом веке.
Цифры меньше 30, Excel предполагает, что цифры представляют годы в двадцать первом веке.
Например, 1 января 29 года рассматривается как 1 января 2029 года, а 1 января 30 года считается 1 января 1930 года.
Преобразование дат в последовательном формате в формат месяц-день-год
Чтобы преобразовать даты из серийного формата в формат месяц-день-год, выполните следующие действия:
-
Перейдите на вкладку « Число » в диалоговом окне « Формат ячеек ».
-
Нажмите Дата под категорией .
-
Выберите Locale . Доступные форматы даты будут отображаться в виде списка под типом .
-
Нажмите « Формат» в разделе « Тип», чтобы посмотреть предварительный просмотр в поле рядом с « Образцом» .
Перейдите на вкладку « Число » в диалоговом окне « Формат ячеек ».
Нажмите Дата под категорией .
Выберите Locale . Доступные форматы даты будут отображаться в виде списка под типом .
Нажмите « Формат» в разделе « Тип», чтобы посмотреть предварительный просмотр в поле рядом с « Образцом» .
После выбора формата нажмите ОК.
Преобразование дат в формате месяц-день-год в последовательный формат
Вы можете конвертировать даты в формате Месяц-День-Год в Серийный формат двумя способами —
-
Использование диалогового окна « Формат ячеек »
-
Использование функции Excel DATEVALUE
Использование диалогового окна « Формат ячеек »
Использование функции Excel DATEVALUE
Использование диалогового окна «Формат ячеек»
-
Перейдите на вкладку « Число » в диалоговом окне « Формат ячеек ».
-
Нажмите Общие под категорией .
Перейдите на вкладку « Число » в диалоговом окне « Формат ячеек ».
Нажмите Общие под категорией .
Использование функции Excel DATEVALUE
Вы можете использовать функцию Excel DATEVALUE для преобразования даты в формат серийного номера . Вам необходимо заключить аргумент Date в «». Например,
= DATEVALUE («6/6/2016») приводит к 42529
Получение сегодняшней даты
Если вам нужно выполнить расчеты на основе сегодняшней даты, просто используйте функцию Excel TODAY (). Результат отражает дату, когда он используется.
Следующий снимок экрана с использованием функции TODAY () был сделан 16 мая 2016 года —
Нахождение рабочего дня после указанных дней
Возможно, вам придется выполнить определенные расчеты на основе ваших рабочих дней.
Рабочие дни исключают выходные и праздничные дни. Это означает, что если вы можете определить свои выходные и праздничные дни, все ваши расчеты будут основаны на рабочих днях. Например, вы можете рассчитать сроки оплаты счета, ожидаемое время доставки, дату следующей встречи и т. Д.
Для таких операций вы можете использовать функции Excel WORKDAY и WORKDAY.INTL .
| S.No. | Описание функции |
|---|---|
| 1. |
РАБОЧИЙ ДЕНЬ Возвращает серийный номер даты до или после указанного количества рабочих дней |
| 2. |
WORKDAY.INTL Возвращает серийный номер даты до или после указанного количества рабочих дней, используя параметры, чтобы указать, какие и сколько дней являются выходными днями. |
РАБОЧИЙ ДЕНЬ
Возвращает серийный номер даты до или после указанного количества рабочих дней
WORKDAY.INTL
Возвращает серийный номер даты до или после указанного количества рабочих дней, используя параметры, чтобы указать, какие и сколько дней являются выходными днями.
Например, вы можете указать 15- й рабочий день с сегодняшнего дня (снимок экрана ниже, сделанный 16 мая 2016 года) с использованием функций TODAY и WORKDAY.
Предположим, 25 мая 2016 года и 1 июня 2016 года — выходные. Тогда ваш расчет будет следующим:
Настройка определения выходного дня
По умолчанию выходные дни — суббота и воскресенье, т.е. два дня. Вы также можете опционально определить выходные дни с помощью функции WORKDAY.INTL . Вы можете указать свои собственные выходные по номеру выходного дня, который соответствует дням выходных, как указано в таблице ниже. Вам не нужно запоминать эти цифры, потому что, когда вы начинаете вводить функцию, вы получаете список номеров и выходные дни в раскрывающемся списке.
| Выходные дни | Выходной номер |
|---|---|
| Суббота Воскресенье | 1 или опущен |
| Воскресение понедельник | 2 |
| Понедельник вторник | 3 |
| Вторник Среда | 4 |
| Среда Четверг | 5 |
| Четверг Пятница | 6 |
| Пятница суббота | 7 |
| Только воскресенье | 11 |
| Только понедельник | 12 |
| Только вторник | 13 |
| Только среда | 14 |
| Только четверг | 15 |
| Только в пятницу | 16 |
| Только суббота | 17 |
Предположим, что если выходные только в пятницу, вам нужно использовать число 16 в функции WORKDAY.INTL.
Количество рабочих дней между двумя указанными датами
Может потребоваться рассчитать количество рабочих дней между двумя датами, например, в случае расчета оплаты сотруднику по контракту, который оплачивается в день.
Вы можете узнать количество рабочих дней между двумя датами с помощью функций Excel NETWORKDAYS и NETWORKDAYS.INTL . Как и в случае с WORKDAYS и WORKDAYS.INTL, NETWORKDAYS и NETWORKDAYS.INTL позволяют указывать праздники, а с помощью NETWORKDAYS.INTL вы можете дополнительно указать выходные.
| S.No. | Описание функции |
|---|---|
| 1. |
NETWORKDAYS Возвращает количество рабочих дней между двумя датами. |
| 2. |
NETWORKDAYS.INTL Возвращает количество рабочих дней между двумя датами, используя параметры, чтобы указать, какие и сколько дней являются выходными |
NETWORKDAYS
Возвращает количество рабочих дней между двумя датами.
NETWORKDAYS.INTL
Возвращает количество рабочих дней между двумя датами, используя параметры, чтобы указать, какие и сколько дней являются выходными
Вы можете рассчитать количество рабочих дней между сегодняшним днем и другой датой с помощью функций TODAY и NETWORKDAYS. На приведенном ниже снимке экрана сегодня 16 мая 2016 года, а дата окончания — 16 июня 2016 года. 25 мая 2016 года и 1 июня 2016 года — выходные.
Опять же, выходные предполагаются суббота и воскресенье. Вы можете иметь собственное определение выходных и рассчитать количество рабочих дней между двумя датами с помощью функции NETWORKDAYS.INTL. На приведенном ниже снимке экрана только пятница определяется как выходные.
Извлечение года, месяца, дня от даты
Вы можете извлечь из каждой даты в списке дат, соответствующего дня, месяца и года, используя функции Excel ДЕНЬ, МЕСЯЦ и ГОД.
Например, рассмотрим следующие даты —
Из каждой из этих дат вы можете извлечь день, месяц и год следующим образом:
Извлечение Дня Недели от Даты
Вы можете извлечь из каждой даты в списке дат, соответствующего дня недели с функцией Excel WEEKDAY.
Рассмотрим тот же пример, что и выше.
Получение даты от года, месяца и дня
Ваши данные могут иметь информацию о Годе, Месяце и Дне отдельно. Вам нужно получить дату, объединяющую эти три значения, чтобы выполнить любой расчет. Вы можете использовать функцию DATE для получения значений даты.
Рассмотрим следующие данные —
Используйте функцию DATE для получения значений DATE.
Подсчет лет, месяцев и дней между двумя датами
Возможно, вам придется рассчитать время, прошедшее с данной даты. Вам может понадобиться эта информация в виде лет, месяцев и дней. Простым примером будет подсчет текущего возраста человека. Это фактически разница между датой рождения и сегодняшним днем. Для этого вы можете использовать функции Excel DATEDIF, TODAY и CONCATENATE.
Выход выглядит следующим образом —