Обработка значений дат в программе Excel

вебинар по Excel

Постановка задачи.

В ходе работы часто приходится делать вычисления с датами. Бухгалтера по заработной плате интересует количество рабочих дней между датами, если человек отправлен в командировку. Бухгалтера материального стола волнует дата очередной поверки счетчиков, определение срока службы основных средств. Экономисту требуется знать срок использования оборудования. Финансовый специалист рассчитывает количество периодов для начисления процентов. В целом, вариантов использования много. Сегодня мы разберемся с большинством типичных задач, которые возникают при работе. Все они сводятся к следующим вариантам:

    • Получение номера месяца, года и т.д. из заданной даты;
    • Нахождение даты, различающейся с указанной на заданное количество дней, недель, месяцев и других вариантов с учетом праздников и выходных или без него;
    • Нахождение количества дней, месяцев, недель и т.д.  между двумя датами.
    • Разберемся с каждым вариантом подробнее.

Получение номера месяца, года и т.д. из заданной даты.

Наверное, это простейший вариант использования функций.  Применяются функции ДЕНЬ, МЕСЯЦ, ГОД. Для работы указывается ячейка с исходной датой или дата прописывается в формуле с использованием кавычек.

=ДЕНЬ(«18.09.2018»)

Функция ДЕНЬ покажет число, т. е. порядковый день месяца, а функции МЕСЯЦ и ГОД извлекут из даты соответственно номер месяца и номер года.

date-work

Рисунок 1

Чтобы узнать номер дня недели, применяем функцию ДЕНЬНЕД. Для нее первой указывают обрабатываемую дату, а затем тип значения. Тип определяет порядок и нумерацию дней недели. Если его не указать, то считается, что неделя начинается с воскресенья, и счет дней недели идет с единицы. Если тип равен 3, то неделя начинается с понедельника, но номер дней идет с нуля. То есть в этом случае понедельник имеет номер 0. В наших условиях первый день – понедельник, счет дней начинается с единицы. Поэтому используем тип, равный 2.

datework-2

Рисунок 2

Так же можно указать тип числами от 11 до 17. В этом случае счет дней будет идти с 1, а первый день будет от понедельника(11) до воскресенья(17). То есть функция =ДЕНЬНЕД(“23.08.2017”;11) и функция =ДЕНЬНЕД(“23.08.2017”;2) дадут одинаковый результат, равный 3. Желающие могут открыть календарь и убедиться, что это действительно был третий день недели по принятому в бывшем СССР варианту, то есть среда.

Для нахождения номера квартала отдельной функции, к сожалению, нет. Однако можно воспользоваться такой формулой:

=ЦЕЛОЕ((МЕСЯЦ(D10)+2)/3)

D10 – это ячейка с исходной датой. Естественно, никто не мешает указать ее явным образом.

datework-3

Рисунок 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 мая.

Решения задачи следующее

datework-4

Рисунок 4

Обратите внимание, что в функции РАБДЕНЬ.МЕЖД можно вообще убрать выходные, оставив только праздники и указов для параметра «выходные дни» строку их 7 нулей. В функции РАБДЕНЬ это невозможно, в ней выходные дни используются автоматически. И еще. Результатом работы обеих функций будет значение в числовом виде. Для преобразования их в формат дат воспользуйтесь форматом даты или примените к результату функцию ТЕКСТ, указав сначала полученное значение, а затем в кавычках нужный формат. Если нужно, чтобы дата выглядела как «число.номер месяца.номер года из 4 цифр», то есть вот так – 15.05.2020  -, тогда формат будет таким «ДД.ММ.ГГГГ».

datework-5

 

Рисунок 5

Для получения даты, отстоящей от указанной на заданное количество месяцев используем функцию ДАТАМЕС. Для нее надо указать начальную дату и количество месяцев, которые надо прибавит или отнять.

datework-6

Рисунок 6

 В качестве варианта используется функция ДАТА. Вначале мы извлекаем из начальной даты ДЕНЬ, МЕСЯЦ и ГОД соответствующими функциями. Затем к нужному промежутку добавляем требуемое количество. И наконец, функцией ДАТА превращаем это все в дату. В функции ДАТА вначале указываем ГОД, затем МЕСЯЦ и последним ДЕНЬ.

Все вместе выглядит так:

Обработка значений дат в программе Excel

Рисунок 7

Обратите внимание, что для этих функций преобразование в формат даты не требуется. Функцию ДАТА применяют и для нахождения даты через заданное количество недель. Надо только это количество умножить на 7, а результат прибавить к значению функции ДЕНЬ.

Обработка значений дат в программе Excel

Рисунок 8

Нахождение количества дней, месяцев, недель и т.д.  между двумя датами.

Иногда в процессе работы возникает задача найти количество дней между двумя датами, количество месяцев и так далее. Первый пример – определить количество отработанных дней между двумя датами для расчета заработной платы.

Для нахождения разницы в КАЛЕНДАРНЫХ днях достаточно от одной даты отнять другую. Если обе даты были изначально именно даты, или если обе даты изначально получены с помощью функций, то результат будет иметь числовой формат без знаков после запятой. Если же одно значение было датой, а второй получено с помощью функции, то результат будет получен в формате даты. В этом случае для него надо задать числовой формат. Если полученное значение поделить на 7, то получим разницу в неделях, которую можно потом округлить для нужной точности. В следующем примере получена разница в днях и неделях, причем недели округлены до полных в верхнюю сторону.

Обработка значений дат в программе Excel

Рисунок 9

Если надо найти  количество РАБОЧИХ дней, то используем функции ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД. Эти функции идентичны функциям РАБДЕНЬ и РАБДЕНЬ.МЕЖД по функционалу, то есть в первой мы применяем стандартные выходные, а во второй выходные указываем сами.

Синтаксис функции ЧИСТРАБДНИ

=ЧИСТРАБДНИ(1;2;3)

1 – начальная дата;

2 – конечная дата;

3 – праздничные дни.

Синтаксис функции ЧИСТРАБДНИ.МЕЖД

=ЧИСТРАБДНИ.МЕЖД(1;2;3;4)

1 – начальная дата;

2 – конечная дата;

3 – выходные дни. Указываются, как и в функции РАБДЕНЬ.МЕЖД;

4– праздничные дни.

Обработка значений дат в программе Excel

Рисунок 10

Обратите внимание на следующую тонкость. Excel справедливо считает, что в первый день вы пришли на работу утром, а вот ушли вечером. Именно поэтому, если указать и начальную и конечную дату одинаково, то он все равно покажет, что один день отработан. Вот почему в приведенном примере мы видим разницу не в 15, а в 16 дней. Если же вы хотите увидеть разницу в рабочих СУТКАХ, тогда надо от результата этих функций отнять единицу.

 Кроме этого, для нахождения разницы между двумя датами применяют недокументированную функцию РАЗНДАТ.

=РАЗНДАТ(1;2;3)

1 – начальная дата

2 – конечная дата

3 – тип расчета, показывает, в каких единицах идет расчет.

Варианты типа:

 – «y» – количество полных лет;

– «m» – количество полных месяцев;

– «d» – количество полных лет;

– «ym» – полных месяцев, без учета лет;

– «yd» – дней, без учета лет;

– «md» – дней, без учета месяцев.

Рисунок 11

Обработка значений дат в программе Excel

Подведем итоги

На нашем уроке мы научились использовать функции и формулы MS Excel для работы с датами. Применяйте их на практике, и вы увидите, как сократится время вашей работы. Желаю всем удачи. Если возникнут вопросы, пишите в комментариях.

Добавить комментарий