Функции даты и времени
Функции для вычислений по дням





Как посчитать календарные или рабочие дни? Можно ли прибавить к дате несколько месяцев? Как это делать правильно, используя функции РАБДЕНЬ, ЧИСТРАБДНИ, ДАТАМЕС, СЕГОДНЯ и пр.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Функции для вычислений по дням

Как посчитать календарные или рабочие дни? Можно ли прибавить к дате несколько месяцев? Как это делать правильно, используя функции РАБДЕНЬ, ЧИСТРАБДНИ, ДАТАМЕС, СЕГОДНЯ и пр. Данный урок содержит примеры расчётов длительности события в календарных днях, вычисление рабочего дня оплаты с помощью функции РАБДЕНЬ, расчёт срока годности товаров с использованием ДАТАМЕС. Функция СЕГОДНЯ может использоваться для актуализации данных по просрочке, например.

Функции для вычислений по дням

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Базовые операции с датами и временем

Темой настоящего урока будут даты - научимся работать с составляющими даты и выполнять расчеты по рабочим дням. В целом, даты - это весьма особенная категория данных в Excel. А почему – давайте разбираться

Создайте новую книгу и в любой ячейке напишите цифру 1 – она будет выровнена по правому краю, если в этой же ячейке к 1 добавить точку, то данные разместятся уже по левой стороне ячейки, но если после точки продолжить набирать положим месяц и год, то при нажатии Enter данные опять окажутся справа, то есть как число? Возникает вполне резонный вопрос – что же это за число такое? На самом деле увидеть число, соответствующее каждой дате достаточно просто – нужно преобразовать данные в числовой формат, и напротив – если для ячейки с числом выбрать формат даты, то после нажатия ОК именно ее вы в ячейке и увидите. Используя сей нехитрый метод ненаучного тыкинга можно увидеть, что цифра один соответствует дате 01.01.1900, а вот подобным же образом угадать, где заканчивается горизонт познаний Excel в датах, достаточно сложно, потому что число это на самом деле велико – 2.958.465, что соответствует 31.12.9999. Таким образом, каждая дата на самом деле является числом, которое для нашего юзерского удобства сразу форматируется программой как текст. Более того, в программе к каждому такому числу привязан индекс от 1 до 7, на основании которых Excel выполняет расчеты по рабочим дням (1 соответствует понедельнику 7 воскресенью)

А теперь, когда мы добрались до истины, что же представляет из себя дата, предлагаю попрактиковаться и разобрать несколько примеров расчетов.

Часто при печати некоторых таблиц (отчетов) необходимо, чтобы на листе присутствовала дата печати. Чтобы не вспоминать о том, что ее необходимо обновлять, используйте функцию СЕГОДНЯ [TODAY] – она не имеет аргументов и всегда возвращает актуальную дату (такую же, которая установлена у вас на компьютере): =СЕГОДНЯ()

 

Собираем день, месяц и год в одну ячейку

Но допустим, что данные по дню, месяцу и году какой-либо даты расположены в отдельных ячейках и нужно собрать их в одну ячейку. В такой ситуации текстовые функции типа СЦЕПИТЬ [CONCATENATE] не подойдут, так как результатом их работы являются текстовые данные, а нам нужно число (!). Поэтому используем функции из специальной категории, в нашем случае функция так и зовется ДАТА [DATE]: =ДАТА(A4;B4;C4) – синтаксис разбирать не будем, он очень простой (указать ячейки с годом, месяцем и днем – более ничего не требуется)

Для решения обратной задачи – например, вытащить в отдельную ячейку только месяц или только год от какой-либо даты – используем функции, которые будут называться так же, как результат их работы, а именно: для вычисления года используем функцию ГОД [YEAR], месяца –МЕСЯЦ [MONTH], дня –ДЕНЬ [DAY]:

 

Как разделить дату и время

А следующая задачка решается чуть более хитрым способом: необходимо разделить по разным ячейкам дату и время, находящиеся в A9 (кстати, там актуальные на текущий момент дата и время – получены при помощи функции ТДАТА [NOW] которая тоже не имеет аргументов и возвращает время и дату соответствующие данным компьютера). Прежде чем приступать к решению, необходимо понять, что представляет из себя время – это весьма наглядно в окошке Формат ячеек [FormatCells], если выбрать Числовой [Number] или Общий формат [General] – целая часть числа это как раз день (количество дней, прошедших начиная с 01.01.1900), а дробная представляет собой долю суток и форматируется как время

Теперь становится понятно – чтобы отобрать дату в отдельную ячейку, нужно вытащить в нее целую часть числа, а с такой задачей легко справится одноименная функция ЦЕЛОЕ [INT]: =ЦЕЛОЕ(A9)

Далее отформатируйте через Формат ячеек [FormatCells] ячейку с результатом таким образом, чтобы нули времени не выводились на экран. Как вариант, для решения этого же вопроса можно использовать функцию ОКРУГЛВНИЗ [ROUNDDOWN] с округлением до нуля знаков после запятой – обязательно эту функцию, чтобы игнорировать правила округления математики (то есть если знак после запятой более четырех, то все равно эти знаки нужно отбросить, не увеличивая предыдущую цифру на 1)

Осталось дело за малым – в отдельную ячейку отобрать время. Самый простой на мой взгляд способ решения задачи – посчитать разницу между полученными данными: =A9-C9

Но можно не искать легких путем и использовать очередную функцию: =ОСТАТ(A9;1) – функция ОСТАТ [MOD] вычисляет остаток от деления на указанное число, поэтому чтобы в любом случае получить остаток в виде всех знаков после запятой, поделим число с датой-временем на 1 – вся толпа знаков после запятой аккурат окажется в остатке

Любым из способов получите результат и отформатируйте ячейку как Время [Time]

 

Расчеты по рабочим дням

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

Есть некоторая дата, с наступлением которой случится определенное событие (будут поставлены товары/материалы выполнены работы/оказаны услуги и т.д.), а через 20 рабочих дней необходимо будет их оплатить (или напротив – получить за них денежные средства). Если раньше подобные расчеты выполнялись при помощи календарика (производственного или классического офисного настенного), то теперь будем использовать Excel и в этом вопросе тоже. Есть функция, которая может вычислить рабочий день, так как умеет отбрасывать даты с индексами 6 или 7, и в ней же есть возможность указать список праздничных дат, из которого будут выбраны и исключены из расчета соответствующие дни (Excel проверит порядковый номер каждой даты из списка праздников и если он попадает в указанных интервал, то будет учтен при расчете). Зовется эта функция РАБДЕНЬ [WORKDAY]: =РАБДЕНЬ(A2;B2;A5:A9)

 

Решить обратную задачу – вычислить, сколько рабочих дней между двумя указанными датами, - можно с помощью функции ЧИСТРАБДНИ [NETWORKDAYS]:

В результате должен получиться 21 рабочий день (не 20 (!), все верно, потому что количество дней между датами вычисляется с и по  включительно – при вычислении календарных дней приходится эту единицу обычно вручную не забыть добавить; при работе же первой из рассмотренных функций стартовая дата имеет нулевой порядковый номер, на счет один мы оказываемся уже на следующей дате – проверьте, возьмите в руки календарик :-) или проще – посчитайте количество дней между двумя соседними рабочими датами и все станет понятно):

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Подсчёт количества ячеек и вычисление средних показателей
Математические и статистические функции
Как посчитать количество ячеек, отвечающих поставленному условию. Как посчитать все заполненные или все пустые ячейки. Расчёт среднеарифметического значения.

Создание и настройка выпадающих списков.
Выпадающие списки.
Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку.

Пользовательские имена ячеек и диапазонов
Имена ячеек
Поиск влияющих и зависимых ячеек. Пользовательские имена - способы создания имен, использование в новых формулах, подстановка имен в старые формулы.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.