Обработка значений дат в программе Excel
Постановка задачи.
В ходе работы часто приходится делать вычисление дат. Бухгалтера по заработной плате интересует количество рабочих дней между датами, если человек отправлен в командировку. Бухгалтера материального стола волнует дата очередной поверки счетчиков, определение срока службы основных средств. Экономисту требуется знать срок использования оборудования. Финансовый специалист рассчитывает количество периодов для начисления процентов. В целом, вариантов использования много. Сегодня мы разберемся с большинством типичных задач, которые возникают при работе. Все они сводятся к следующим вариантам:
-
- Получение номера месяца, года и т.д. из заданной даты;
- Нахождение даты, различающейся с указанной на заданное количество дней, недель, месяцев и других вариантов с учетом праздников и выходных или без него;
- Нахождение количества дней, месяцев, недель и т.д. между двумя датами.
- Разберемся с каждым вариантом подробнее.
Получение номера месяца, года и т.д. из заданной даты.
Наверное, это простейший вариант использования функций. Применяются функции ДЕНЬ, МЕСЯЦ, ГОД. Для работы указывается ячейка с исходной датой или дата прописывается в формуле с использованием кавычек.
=ДЕНЬ(«18.09.2018»)
Функция ДЕНЬ покажет число, т. е. порядковый день месяца, а функции МЕСЯЦ и ГОД извлекут из даты соответственно номер месяца и номер года.
Рисунок 1
Чтобы узнать номер дня недели, применяем функцию ДЕНЬНЕД. Для нее первой указывают обрабатываемую дату, а затем тип значения. Тип определяет порядок и нумерацию дней недели. Если его не указать, то считается, что неделя начинается с воскресенья, и счет дней недели идет с единицы. Если тип равен 3, то неделя начинается с понедельника, но номер дней идет с нуля. То есть в этом случае понедельник имеет номер 0. В наших условиях первый день – понедельник, счет дней начинается с единицы. Поэтому используем тип, равный 2.
Рисунок 2
Так же можно указать тип числами от 11 до 17. В этом случае счет дней будет идти с 1, а первый день будет от понедельника(11) до воскресенья(17). То есть функция =ДЕНЬНЕД(“23.08.2017”;11) и функция =ДЕНЬНЕД(“23.08.2017”;2) дадут одинаковый результат, равный 3. Желающие могут открыть календарь и убедиться, что это действительно был третий день недели по принятому в бывшем СССР варианту, то есть среда.
Для нахождения номера квартала отдельной функции, к сожалению, нет. Однако можно воспользоваться такой формулой:
=ЦЕЛОЕ((МЕСЯЦ(D10)+2)/3)
D10 – это ячейка с исходной датой. Естественно, никто не мешает указать ее явным образом.
Рисунок 3
В данном случае 16 июля 2020 года действительно относится к третьему кварталу.
Если нужно найти номер недели, используют функцию со схожим названием НОМНЕДЕЛИ. В ней надо указать дату, для которой надо найти номер недели, а затем тип отчета.
Для этой функции могут использоваться две разные системы.
- Система 1 Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
- Система 2 Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1. Эта методика определена в стандарте ISO 8601, который широко используется в Европе для нумерации недель
Используют следующие типы:
1 или опущен – Воскресенье, 1 система отчета;
2 – Понедельник, 1 система отчета;
11 – Понедельник, 1 система отчета;
12 – Вторник, 1 система отчета;
13 – Среда, 1 система отчета;
14 – Четверг, 1 система отчета;
15 – Пятница, 1 система отчета;
16 – Суббота, 1 система отчета;
17 – Воскресенье, 1 система отчета;
21 – Понедельник, 2 система отчета.
Примеры использования:
=НОМНЕДЕЛИ(“21.05.2018”;11) – результат будет 21, неделя начинается с понедельника.
=НОМНЕДЕЛИ(“21.05.2018”;17) результат будет 21, неделя начинается с воскресенья.
При необходимости найти номер недели в соответствии с форматом ISO применяют функцию
=НОМНЕДЕЛИ.ISO, для которой достаточно указать только дату.
Нахождение даты, отстоящей от указанной на заданное количество дней, недель, месяцев с учетом праздников и выходных или без него.
Для того чтобы найти дату, отстоящую от указанной на заданное количество дней используются функции РАБДЕНЬ и ЧИСТРАБДНИ. Разница между ними только в том, что ЧИСТРАБДНИ позволяет задать произвольные выходные дни, а РАБДЕНЬ использует стандартный для данной страны вариант. Для нашей страны это суббота и воскресенье.
Функция РАБДЕНЬ пишется так:
=РАБДЕНЬ(1;2;3)
1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!
2 – количество рабочих дней. Функция найдет дату последнего дня из указанного количества
3 – праздники. Показывают в виде диапазона, прописывают в виде массива в фигурных скобках. Если в рабочем периоде праздников нет, то не указывают. Либо указывают на диапазон с ними.
Для функции РАБДЕНЬ.МЕЖД используют такую запись:
= РАБДЕНЬ.МЕЖД(1;2;3;4)
1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!
2 – количество рабочих дней, функция покажет дату последнего из них.
3 – выходные дни. Excel предлагает на выбор числовые коды для выходных, но можно поступить проще. Надо указать дни недели в виде строки из единиц и нулей. Первый знак будет относиться к первому дню недели (у нас это понедельник, у кого-то воскресенье). Единицами указывается выходные дни. Например, если для стандартных выходных в субботу и воскресенье используется запись – «0000011». Не забываем про кавычки!
4 – праздничные дни
Разберем такую пример. На выполнение работы по ремонту требуется согласно нормативу 15 рабочих дней. Какого числа работы должны быть закончены в Казахстане, если они начаты 22 апреля 2020 года при условии стандартных выходных или выходных по понедельникам. Праздничные дни Казахстана в мае 2020 года – 1 мая, 7 мая, 9 мая. Пи этом так как 9 мая выпало на субботу, поэтому согласно правилам и постановлению день отдыха перенесен на 8 мая.
Решения задачи следующее
Рисунок 4
Обратите внимание, что в функции РАБДЕНЬ.МЕЖД можно вообще убрать выходные, оставив только праздники и указов для параметра «выходные дни» строку их 7 нулей. В функции РАБДЕНЬ это невозможно, в ней выходные дни используются автоматически. И еще. Результатом работы обеих функций будет значение в числовом виде. Для преобразования их в формат дат воспользуйтесь форматом даты или примените к результату функцию ТЕКСТ, указав сначала полученное значение, а затем в кавычках нужный формат. Если нужно, чтобы дата выглядела как «число.номер месяца.номер года из 4 цифр», то есть вот так – 15.05.2020 -, тогда формат будет таким «ДД.ММ.ГГГГ».
Рисунок 5
Для получения даты, отстоящей от указанной на заданное количество месяцев используем функцию ДАТАМЕС. Для нее надо указать начальную дату и количество месяцев, которые надо прибавит или отнять.
Рисунок 6
В качестве варианта используется функция ДАТА. Вначале мы извлекаем из начальной даты ДЕНЬ, МЕСЯЦ и ГОД соответствующими функциями. Затем к нужному промежутку добавляем требуемое количество. И наконец, функцией ДАТА превращаем это все в дату. В функции ДАТА вначале указываем ГОД, затем МЕСЯЦ и последним ДЕНЬ.
Все вместе выглядит так:
Рисунок 7
Обратите внимание, что для этих функций преобразование в формат даты не требуется. Функцию ДАТА применяют и для нахождения даты через заданное количество недель. Надо только это количество умножить на 7, а результат прибавить к значению функции ДЕНЬ.
Рисунок 8
Нахождение количества дней, месяцев, недель и т.д. между двумя датами.
Иногда в процессе работы возникает задача найти количество дней между двумя датами, количество месяцев и так далее. Первый пример – определить количество отработанных дней между двумя датами для расчета заработной платы.
Для нахождения разницы в КАЛЕНДАРНЫХ днях достаточно от одной даты отнять другую. Если обе даты были изначально именно даты, или если обе даты изначально получены с помощью функций, то результат будет иметь числовой формат без знаков после запятой. Если же одно значение было датой, а второй получено с помощью функции, то результат будет получен в формате даты. В этом случае для него надо задать числовой формат. Если полученное значение поделить на 7, то получим разницу в неделях, которую можно потом округлить для нужной точности. В следующем примере получена разница в днях и неделях, причем недели округлены до полных в верхнюю сторону.
Рисунок 9
Если надо найти количество РАБОЧИХ дней, то используем функции ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД. Эти функции идентичны функциям РАБДЕНЬ и РАБДЕНЬ.МЕЖД по функционалу, то есть в первой мы применяем стандартные выходные, а во второй выходные указываем сами.
Синтаксис функции ЧИСТРАБДНИ
=ЧИСТРАБДНИ(1;2;3)
1 – начальная дата;
2 – конечная дата;
3 – праздничные дни.
Синтаксис функции ЧИСТРАБДНИ.МЕЖД
=ЧИСТРАБДНИ.МЕЖД(1;2;3;4)
1 – начальная дата;
2 – конечная дата;
3 – выходные дни. Указываются, как и в функции РАБДЕНЬ.МЕЖД;
4– праздничные дни.
Рисунок 10
Обратите внимание на следующую тонкость. Excel справедливо считает, что в первый день вы пришли на работу утром, а вот ушли вечером. Именно поэтому, если указать и начальную и конечную дату одинаково, то он все равно покажет, что один день отработан. Вот почему в приведенном примере мы видим разницу не в 15, а в 16 дней. Если же вы хотите увидеть разницу в рабочих СУТКАХ, тогда надо от результата этих функций отнять единицу.
Кроме этого, для нахождения разницы между двумя датами применяют недокументированную функцию РАЗНДАТ.
=РАЗНДАТ(1;2;3)
1 – начальная дата
2 – конечная дата
3 – тип расчета, показывает, в каких единицах идет расчет.
Варианты типа:
– «y» – количество полных лет;
– «m» – количество полных месяцев;
– «d» – количество полных лет;
– «ym» – полных месяцев, без учета лет;
– «yd» – дней, без учета лет;
– «md» – дней, без учета месяцев.
Рисунок 11
Подведем итоги
На нашем уроке мы научились использовать функции и формулы MS Excel для работы с датами. Применяйте их на практике, и вы увидите, как сократится время вашей работы. Желаю всем удачи. Если возникнут вопросы, пишите в комментариях.
