Основы работы с датами и временем в Excel

Видео

Как обычно, кому нужно быстро – смотрите видео. Подробности и нюансы – в тексте ниже:

Как вводить дату и время в Excel

If we keep in mind the regional settings, then Excel allows you to enter the date in very different ways – and understands them all:

Внешний вид (отображение) даты в ячейке может быть самым разным (с годом или без него, месяцем в виде числа или слова и т.п.) и задается через контекстное меню – клик правой кнопкой мыши по ячейке, а затем Формат ячейки (Формат ячеек):

Время вводится в ячейки с помощью двоеточия. Например

16:45

При желании можно дополнительно указать количество секунд – вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу через пробел, то есть 

27.10.2012 16:45

Быстрый ввод даты и времени

Чтобы ввести сегодняшнюю дату в текущую ячейку, вы можете использовать сочетание клавиш Ctrl + Ж (или CTRL + SHIFT + 4 если у вас другой системный язык по умолчанию).

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

Если вам часто приходится вводить в ячейки листа разные даты, то гораздо удобнее сделать это с помощью всплывающего календаря:

Если вы хотите, чтобы ячейка всегда содержала актуальную сегодняшнюю дату, лучше использовать функцию СЕГОДНЯ (CЕГОДНЯ):

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (щелкните правой кнопкой мыши по ячейке Формат ячейки - вкладка Номер регистрации - Общие), вы можете увидеть интересную картину:

То есть с точки зрения Excel, 27.10.2012 15:42 вечера = 41209,65417

Фактически Excel хранит и обрабатывает любую дату именно так – как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (принятого за точку отсчёта) до текущей даты. А дробная часть (0,65417) соответственно доля дня (1 день = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не может работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но мы это переживем! 😉
• Во-вторых, в Excel можно производить любые математические операции с датами и временем. Именно потому, что это на самом деле числа! Но это уже открывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием – из конечной даты вычитаем начальную дату и переводим результат в Общие (Общее) числовой формат для отображения разницы в днях:

Количество рабочих дней между двумя датами

Здесь ситуация немного сложнее. Суббота, воскресенье и праздничные дни не учитываются. Для такого расчета лучше использовать функцию ЧИСТЫЕ РАБОТНИКИ (ЧИСТЕВЫЕ ДНИ) из категории Дата и время. В качестве аргументов этой функции необходимо указать даты начала и окончания, а также ячейки с датами выходных дней (праздничные дни, больничные, отпуска, выходные и т.д.):

Внимание: Эта функция появилась в стандартном наборе функций Excel начиная с версии 2007 года. В старых версиях необходимо сначала подключить дополнение Пакет анализа. Для этого зайдите в меню Сервис – Дополнения (Инструменты — Надстройки) и установите флажок рядом с Пакет анализа (Пакет инструментов Analysisys). После этого в Мастере функций в категории Дата и время появится нужная нам функция ЧИСТЫЕ РАБОТНИКИ (ЧИСТЕВЫЕ ДНИ).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Опыт.

О том, как его правильно рассчитать, лучше прочитать здесь.

Сдвинуть дату на указанное количество дней

Поскольку в системе отсчета дат Excel за единицу принимается один день (см. выше), то для расчета даты, отстоящей, скажем, на 20 дней от заданной, достаточно прибавить к дате это число.

Сдвинуть дату на указанное количество рабочих дней

Эту операцию выполняет функция WORKDAY (РАБОЧИЙ ДЕНЬ). Он позволяет рассчитать дату вперед или назад относительно даты начала на желаемое количество рабочих дней (с учетом субботы, воскресенья и праздничных дней). Использование этой функции точно такое же, как использование функции ЧИСТЫЕ РАБОТНИКИ (ЧИСТЕВЫЕ ДНИ) описано выше.

Расчет дня недели

Разве ты не родился в понедельник? Нет? Конечно? Это легко проверить с помощью функции ДНЕЙ (БЕДНЫЙ ДЕНЬ)из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).  

Расчет временных интервалов

Поскольку время в Excel, как уже говорилось выше, — это то же число, что и дата, но только его дробная часть, то со временем, как и с датой, возможны любые математические операции — сложение, вычитание и т. д.

Здесь есть только один нюанс. Если при сложении нескольких временных интервалов сумма оказалась больше 24 часов, то Excel обнулит ее и снова начнет суммировать с нуля. Чтобы этого не произошло, нужно применить формат к конечной ячейке 37:30:55:

• Как рассчитать возраст (стаж) полными годами-месяцами-днями
• Как сделать выпадающий календарь, чтобы быстро вводить любую дату в любую ячейку.
• Автоматически добавлять текущую дату в ячейку при вводе данных.
• Как рассчитать дату второго воскресенья февраля 2007 года и т.д.