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