Довольно часто при работе с датами в электронной таблице Excel возникает необходимость указать промежуток времени между ними, который измеряется в днях. В Excel существуют различные способы сделать это. Давайте посмотрим на них.
Расчет разности дат
См. также "Как сделать ячейки одинаковыми в Excel".
Чтобы правильно работать с датами, необходимо сначала правильно отформатировать ячейки. В противном случае у вас могут возникнуть проблемы с корректным отображением дат, что помешает их дальнейшей обработке.
Фактически, Excel способен самостоятельно определять формат ячейки, когда вы вводите в нее информацию. Однако этот механизм не всегда работает так, как задумано, поэтому нелишним будет установить формат ячейки самостоятельно или проверить, правильно ли программа его определила.
- Сначала выделите целевой диапазон ячеек, удерживая нажатой левую кнопку мыши.
- Щелкните правой кнопкой мыши на выделенной области и в появившемся контекстном меню выберите Формат ячеек. Того же эффекта можно добиться, используя комбинацию клавиш Ctrl+1.
- Перед вами окно Параметры формата ячеек:
- по умолчанию мы должны оказаться на вкладке "Число" (если это не так, переключитесь на нее);
- В списке "Числовые форматы" найдите пункт "Дата" и нажмите на него;
- В поле "Тип" выберите желаемое представление данных, в соответствии с которым они будут отображаться на листе;
- Нажмите OK.
- Теперь все значения, введенные в ячейки выделенной области, будут восприниматься программой как данные в формате "Данные".
Метод 1: использование формулы вычитания
Начнем, пожалуй, с самого простого метода - вычитания двух дат, представленного простой формулой.
- Мы вводим две даты, между которыми мы будем определять разницу дней, в две разные ячейки предварительно отформатированного диапазона.
- Теперь выберите ячейку, в которой вы хотите отобразить результат наших вычислений.
ВАЖНО: Количество дней является простым целым числом, поэтому ячейка для его отображения должна быть отформатирована иначе, чем "Дата", а именно "Итого". В противном случае вывод будет в формате даты, что не позволит вам оценить достоверность расчета. Чтобы определить текущий формат ячейки, просто посмотрите на соответствующее поле, расположенное в разделе инструмента "Число" на ленте вкладки "Главная". - В нашем случае он имеет значение "Дата". Чтобы изменить его, снова вызовите окно Формат ячеек и по приведенному выше алгоритму установите формат чисел "Общий". Для сохранения изменений нажимаем кнопку OK.
- Теперь мы можем перейти непосредственно к расчету:
- Введите знак "=" в итоговую ячейку;
- щелкните в ячейке конечной даты;
- и введите "-" (знак минус) с клавиатуры;
- щелкните в ячейке с начальной датой.
- Нажмите Enter, чтобы получить результат. Результатом будет целое число - количество дней между двумя датами.
Метод 2: функция РАЗНДАТ
Другой метод - определение разницы между двумя датами с помощью функции RAZNDAT.
Функция имеет следующий синтаксис: =RASANDAT(начальная_дата;конечная_дата;единица).
Если с первыми двумя аргументами все понятно - их суть соответствует названию, то на третьем ("unit") стоит остановиться подробнее, поскольку он позволяет изменить результат использования функции, выбрав единицу вычисления разницы между датами:
- "d" - количество дней между датами;
- "m" - количество полных месяцев между датами;
- "y" - количество полных лет между датами;
- "YM" - это разница между датами в месяцах;
- "MD" - разница между датами в днях, исключая месяцы и годы;
- "YD" - это разница между датами в днях, исключая годы.
В нашем случае мы покажем пример использования аргумента 'YD', поскольку целью вычисления является определение количества дней между датами в пределах года.
Основное отличие от обычного вычитания одной даты из другой заключается в порядке аргументов функции: первый аргумент - начальная дата (более ранняя), второй - конечная дата (более поздняя). Опишем алгоритм расчета ниже.
- Выберите целевую ячейку и проверьте ее формат описанным выше методом.
- Описанная функция недоступна в Мастере функций, поэтому введите ее в ячейку вручную, строго соблюдая синтаксис.
- Нажмите Enter и проверьте результат. Как мы видим, оно совпадает с тем, которое мы получили с помощью формулы простого вычитания. Так что мы все сделали правильно.
Метод 3: функция ЧИСТРАБДНИ для определения количества рабочих дней
Третий описанный метод позволяет рассчитать количество дней с дополнительным условием исключения праздничных дней (выходных и праздничных).
Этот метод использует функцию NUMBERDAYS со следующим синтаксисом: =NUMBERDAYS(begin_date;end_date;[holidays]).
Первые два аргумента аналогичны функции DIVISION. Последний аргумент является необязательным, т.е. функция будет работать и без него. Однако этот аргумент позволяет включить в расчет праздничные дни, поскольку по умолчанию общее количество праздничных дней вычитается только по субботам и воскресеньям.
Теперь выполним расчет:
- Поскольку рассматриваемая функция находится в Мастере функций, мы будем использовать ее для наглядности: перейдите к ячейке, в которую мы будем выводить результат, и нажмите на кнопку "Вставить функцию" (fx) в левой части панели формул.
- В открывшемся окне Мастера функций:
- выберите категорию "Полный алфавитный список";
- найдите и щелкните по строке "NUMERIC" в списке операторов;
- нажмите ОК.
- Перед вами появится окно аргументов функции. Вы можете ввести адреса ячеек вручную или щелкнуть по нужной ячейке в поле для заполнения значений аргументов. Выберите начальную дату, конечную дату и праздник (адрес ячейки) и нажмите OK.
- Предположим, что на этот раз мы не задали формат ячейки итогов как "Общий". Затем мы получим результат, отображаемый в виде даты.
- Поэтому нам необходимо снова проверить формат по описанному выше алгоритму и изменить его на "General".
Заключение
Используя описанные выше методы, довольно легко подсчитать количество дней между двумя датами, как с дополнительными критериями, так и без них. Формула вычитания и функция DIVISION идеально подходят для подсчета количества дней, а функция ЧИСЛО полезна для подсчета количества рабочих дней между двумя датами. Окончательный выбор зависит от поставленной задачи.
