Как в экселе посчитать временной промежуток

Вычисление разницы во времени

Предположим, что вы хотите узнать, сколько времени сотруднику требуется на завершение работы в строке сборки или заказ на питание для обработки в пиковые часы. Существует несколько способов вычисления разницы между двумя значениями времени.

Представить результат в стандартном формате времени

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

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

1. Выделите ячейку.
2. На вкладке Главная в группе Число щелкните стрелку рядом с полем Общие и выберите другие числовые форматы.
3. В диалоговом окне Формат ячеек в списке Категория выберите настраиваемый формат, а затем в поле Тип выберите пользовательский формат.

Для форматирование времени используйте функцию ТЕКСТ. При использовании кодов формата времени количество часов не превышает 24, минуты никогда не превышают 60, а секунды никогда не превышают 60.

Пример таблицы 1. Представляем результат в стандартном формате времени

Скопируйте таблицу ниже на пустой лист и при необходимости измените ее.

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

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию). Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать — как именно копировать выделенную дату: Если Вам часто приходится вводить различные даты в ячейки листа, то гораздо удобнее это делать с помощью всплывающего календаря: Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY) :

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

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку: То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417 На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0) Из всех этих фактов следуют два чисто практических вывода:

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

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

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

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

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

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS) .

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

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

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

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

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

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

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

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

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

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

Ссылки по теме

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

Суммы с датами в Excel

В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.

В примере показано, ячейка H7 содержит формулу:

Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.

Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.

Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:

Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).

Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).

Сумма, если Дата больше, чем

В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.

В примере показано, ячейка H4 содержит формулу:

Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).

Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).

ДАТА как ссылка на ячейку

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

где A1-ссылка на ячейку, которая содержит действительную дату.

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:

Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

Как суммировать значения между двумя датами

Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.

Давайте рассмотрим как это сделать.

У нас есть таблица с данными по продажам за каждый день. Для выполнения задачи нам потребуется функция СУММЕСЛИМН.

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

• диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
• диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
• [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

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

Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.

Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:

После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.

Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.

Больше лайфхаков в нашем Telegram Подписаться

Как работает эта формула

В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН , которые помогают нам суммировать данные в указанном диапазоне дат.

Давайте разложим формулу на составные части для понимания ее работы:

• Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
• Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
• Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
• Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018

Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Как суммировать значения между двумя динамическими датами

На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?

В этом случае нам поможет следующая формула:

Как работает эта формула

В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.

Здесь, в первом критерии для вычисления нужной даты мы указываем функции условие, при котором нужно суммировать данные за период равный или меньше текущей даты.

Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.

Таким образом, диапазон двух дат в формуле теперь выглядит как: «Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня».

Если у вас остались вопросы по этому примеру оставляйте их в комментариях.

Больше лайфхаков в нашем ВК Подписаться
Оцени запись
Написать комментарий Отменить ответ
Любовь 22.07.2019 в 10:39

Формула работает прекрасно. Спасибо. Ваши примеры значительно облегчают работу. Хотелось бы узнать, каким образом возможно производить одновременно отборку по диапазону и видам товара, проданного в этот период?

Владислав Каманин автор 21.06.2020 в 22:56
Любовь, добрый день, достаточно добавить условий в функцию СУММЕСЛИМН относящиеся к видам товара
Елена 20.08.2019 в 15:31
Спасибо! За формулу и доступное объяснение.
Владислав Каманин автор 15.02.2021 в 23:14
Елена, рад, что статья вам пригодилась!
Владислав 31.01.2020 в 16:09

А есть ли способ, все-таки считать динамически сумму по интервалу, не прибегая к вводу в формулу константных значений и через сегодня() У меня к примеру задача — обращаться к такому же отрезку в прошлом году (понятное дело, можно идентифицировать сами отрезки и их сопоставлять), но возможно есть способ именно к датам привязаться? потому что формула не работает с ссылками на ячейки

Яна 18.06.2020 в 16:58

Владислав, я сама долго искала, как эту формулу привязать к ячейке с датой. И нашла)). Используйте эту формулу из примера в статье: =СУММЕСЛИМН(B2:B18;A2:A18;”=”&СЕГОДНЯ()-6). Только вместо СЕГОДНЯ() вставьте ссылку на ячейку с датой.
Я считала средневзвешенный курс валюты для заданного диапазона дат, т.е. использовала функцию СРЗНАЧЕСЛИМН. Синтаксис аналогичный СУММЕСЛИМН. Моя формула: =СРЗНАЧЕСЛИМН(‘курс валюты’!$C:$C;’курс валюты’!$A:$A;»>=»&A17;’курс валюты’!$A:$A;»<="&B17), где в столбце С — курсы валют на конкретную дату в столбце А, заданный интервал дат: от А17 до В17 на другом листе — т.е. в формуле есть ссылки на конкретные ячейки с датами и они работают. Удачи!

Руслан 05.01.2021 в 17:19

Огромное спасибо. Хотелось бы понять, почему не срабатывает, если знаки =, не заключить в кавычки? Просто я долго бился над этой задачей пока нашел Ваш пример, единственное что я не догадался заключить в кавычки, и так и не понял почему их нужно ограничивать?

Владислав Каманин автор 06.03.2021 в 22:46

Руслан, здравствуйте, в кавычки важно заключать, так как мы строим выражение из двух элементов: знаков и функции СЕГОДНЯ через амперсанд.

Николай 14.01.2021 в 22:00

Спасибо за доступное объяснение как пользоваться формулой.
Остался один вопрос, мне данные выгружаются с датой в формате 2021-01-12T13:01:00, время продаж у всех разное, использую формулу что б разнести продажи по каждому товару по дням, в формате 2021-01-12 даты проблем нет(по апи данные подтягивают формат даиы вместе со временем), но когда присутствует время формула не работает, подскажите пожалуйста как решить эту задачу

Владислав Каманин автор 06.03.2021 в 22:47
Николай, добрый день, важно убедиться, что значение со временем в формате даты, а не текста.
Ученик 22.08.2021 в 13:35
Отлично .спасибо
Владислав Каманин автор 27.08.2021 в 17:54
Рад помочь!
Елена 26.08.2021 в 09:37
А с числовыми данными эта формула работает? Попробовала — вышла ошибка #Знач!
Владислав Каманин автор 27.08.2021 в 17:55
Да, конечно, проверьте вашу дату. Скорее всего она указана в текстовом формате.
алекесандр 16.12.2021 в 10:18

Добрый день Владислав! Интересная статья!
Мне надо посчитать сумму данных за неделю (с пятницы по текущую пятницу) ,для вашего примера я сделал формулу
=СУММЕСЛИМН($B:$B;$A:$A;»=»&СЕГОДНЯ()-6)

Владислав Каманин автор 18.12.2021 в 12:39
Александр, спасибо ��
Олег 04.02.2022 в 13:14

Владислав, спасибо Вам огромное за пример с амперсандом. Не знал, как ячейку с датой привязать к условию. Еще раз огромное спасибо.

Владислав Каманин автор 04.02.2022 в 15:07
Олег, рад помочь!
Болот 15.06.2022 в 07:18

Здравствуйте Владислав.
Подскажите пожалуйста, у меня есть два листа в одной книге. В 1-листе таблица по датам и видам товаров. Во 2-листе я используя вашу формулу беру данные (суммы) сперва по нужному мне товару и в нужной мне промежутке дат. Также вместо указания даты в условии я указал ссылку в ячейки где вводятся нужные даты. Но результат дает 0. Как сделать правильно?
Вот пример формулы: СУММЕСЛИМН(Лист1!2:С28;A2:A28;Лист2!А2;Лист1!B2:B28;”Лист2!>=В1″;В2:В28;”Лист2!<=В2″) Формула стоит в Лист2

Болот 15.06.2022 в 07:43
Вроде нашел причину. Спасибо за ваш труд
Дмитрий 25.09.2022 в 17:11

Добрый день есть такой вопрос, у меня таблица по продаже и закупке NFT рынка и у меня есть много позиций которые не объединены в одну таблицу, Могли бы вы помочь найти ошибку формуле или дать ответ на вопрос (формула) =СУММЕСЛИМН(B12+I12+P12+U12+P22+U22+I22+B28+B20+I30+P32+U32+I40+B38+B46+I48+P42+U42+B54;B12+I12+P12+U12+P22+U22+I22+B28+B20+I30+P32+U32+I40+B38+B46+I48+P42+U42+B54;Y50)
Мне нужна формула которая будет работать как механизм то есть у меня есть итоги за месяца но они привязаны к одной таблице и мне нужно что бы когда бюджет заканчивался и я начинал использовать новый бюджет (Старый бюджет+ Чист прибыль с зароботка), то формула фиксировала заработок и больше не прибавляла новые позиции и т д

Максим 17.11.2022 в 16:45

Подскажите пожалуйста, из-за чего может не работать формула?
=СУММЕСЛИ(ABC!C2:C100;»>=25.10.2022″;ABC!D2:D100), где ABC!C — столбец с датами, а ABC!D — столбец со значениями. когда принудительно сравниваешь дату из таблицы с датой обычной — работает корректно, как только запихиваешь в СУММЕСЛИ — нули. Если убрать условие , оставив только = — работает без проблем.

Полина 03.05.2023 в 19:43

Добрый день. подскажите в чем ошибка =СУММЕСЛИМН(D4:D;A4:A;″>=01.01.2022″;A4:A;″ <=31.12.2022″)
нужно сложить продажи за определенный год, соответственно D4:D это столбец с значением продаж
а столбец A4:A это даты
если я хочу вывести данные за 22 год я ставлю диапазон с 1.01.22 по 31.21.22
но почему то формула пишет Ошибка.Синтаксическая ошибка в формуле. что я делаю не так?

Света 02.08.2023 в 16:48

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