Сравнение времени в excel

Обновлено: 08.07.2024

Мы проверили на предыдущем уроке, что дата в Excel – это число количества дней прошедших от 01.01.1900г. А сейчас убедимся что время в Excel – это дробная часть числа, которая представляет собой период суток.

Работать с временем приходиться не редко. Например когда нужно отделу кадров сформировать отчет по балансу рабочего времени сотрудников предприятия. Или производственному отделу нужно посчитать сколько потребуется работо-часов фрезерному станку на изготовления деталей мелкосерийного производства. Время нужно считать инженерам для анализа производительности оборудования при определенных количествах операций.

Как посчитать сумму времени в Excel?

Каждая дробная часть чисел в Excel может отображаться в часах и минутах (в зависимости от выбранного формата отображения).

Данный факт лучше усвоить на конкретном примере:

Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5(полдень); 0,75 (3/4 дня).
Выделите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) задайте формат «Время», как показано на рисунке:

Чтобы сложить время в Excel по формуле или с помощью функции:=СУММ() нужно использовать специальный формат.

В ячейку А4 введите:=СУММ(A1:A3). Как видно вместо 36 часов мы видим только 12 – это время следующего дня. А чтобы отобразить сумму часов меняем формат ячейки.
Открываетм окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» вводим: [ч]:мм:сс. И нажимаем ОК.

Теперь отображается количество суммированных часов и минут.

Как посчитать разницу во времени в Excel?

Данный метод отображения времени весьма полезный. Допустим нам нужно узнать разницу между временем в минутах. В ячейку B2 введите формулу:=A2-A1. Установите теперь для ячейки «Все форматы» и на этот раз в поле «Тип:» вводим: [мм]. И нажимаем ОК.

Так нам удалось перевести время в минуты. Если мы в эту же ячейку введем число 1 (или время 24:00 что по сути одно и то же), то мы узнаем что в сутках 1440 минут. Так фактически можно в Excel посчитать любое время в минутах.

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

Учет времени на протяжении суток

На следующем примере посчитаем, какая часть суток прошла от полночи до 9:45.

В ячейки A1:A3 введите 9:45.
Перейтите на A1 и вызовите окно «Формат ячеек» (CTRL+1). На закладке «Число», из списка «Числовые форматы» выберите «Дробный».
Перейдите на A2 и задайте таким же способом «Процентный» формат (или просто нажмите комбинацию горячих клавиш CTRL+SHIFT+5).
Перейдите на A3 и удалите формат ячейки. А из предыдущих уроков вы знаете, что удалить формат это присвоить формат по умолчанию «Общий». Для этого можно нажать комбинацию CTRL+SHIFT+` или использовать выше описанный способ.

Как видите 9:45 – это 2/5 частей суток. Проще говоря, прошло пока 40,63% суток.

Как поставить текущее время в Excel?

Автоматический ввод текущего времени в Excel выполняется комбинацией горячих клавиш CTRL+SHIFT+;. Автоматическая вставка текущей даты и времени в Excel отличается использованием клавиши SHIFT.

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

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

2 популярных способа сравнения 2 дат в Excel

Итак, если нам требуется сравнить две даты между собой, это можно сделать двумя основными методами в зависимости от поставленной задачи. Давайте узнаем, какие они. В этом нет ничего сложного, формулы довольно простые и почти не отличаются от тех, которые применяются для работы с числами. Не забываем о том, что дата воспринимается электронной таблицей, как число, и обрабатывается аналогичным образом.

Первая дата больше второй, или нет

В этом случае нужно использовать аналогичную формулу, которая используется для проверки чисел: =A1>A2. Если эта проверка подтверждается, то в ячейку, куда была записана эта формула, возвращается значение “ИСТИНА”. Если же первое число меньше второго, то тогда будет записано “ЛОЖЬ”. Аналогично, только зеркально противоположно с формулой =A1<A2. Если в результате проверки оказывается, что первое значение оказалось меньше второго, то в ячейку будет возвращено логическое значение «ИСТИНА». Если же больше – «ЛОЖЬ».

Функция ЕСЛИ, значение ИСТИНА или ЛОЖЬ

Это уже более сложный вариант, который дает возможность не только говорить, действительно ли одна дата больше другой, но и указывать, на какое количество дней. Чтобы это сделать, необходимо нажать по ячейке и записать туда формулу =ЕСЛИ(A2>B2;»Первая дата больше второй на»&» «&A2-B2&» «&»дней»;»Первая дата меньше второй на»&» «&B2-A2&» «&»дней»)

Здесь мы осуществляем проверку двух дат, и исходя из того, что у нас получилось в итоге, выводим результат, на сколько дней первая дата больше, чем вторая. Чтобы это сделать, нами был использовал оператор &, который объединяет две строки текста между собой и объединенный результат возвращает в ячейку. Далее получившийся текст можно использовать сам по себе или в других формулах.

Предположим, вы работаете в пенсионном фонде и хотите определить трудовой стаж в количестве лет, дней, месяцев и так далее. Это можно сделать с помощью функции РАЗНДАТ .

Ее синтаксис элементарный.

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

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

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

Разница 2 дат в днях

Чтобы определить разницу между двумя датами в днях, необходимо воспользоваться такой формулой.

Но к сожалению, эта формула не работает настолько идеально, как хотелось бы. Давайте приведем небольшой пример для большей наглядности.

Предположим, начальной датой у нас является 23.02.2009, а конечной – 24.02.2009.

В результате формулой будет возвращено значение 1, что равняется количеству дней, которое прошло с момента исходной даты (отсчет начинается с нуля). Именно из-за того, что отсчет в этом случае начинается с нуля, нельзя использовать эту формулу в чистом виде, например, чтобы рассчитать количество дней, которое отработал человек. Ведь очевидно, что если человек начал работать 23 февраля, а 24 числа прекратил работать, то он по факту отработал 2 дня, а не один.

Та же самая история с количеством месяцев. Если начальной датой служит 28 февраля, а конечная – первое марта, и оба числа относятся к 2008 году, то результат будет 2 дня. И это правильно.

Чтобы решить все проблемы с отображением месяцев в Excel, существует специальная формула ЦЕЛОЕ() , которая округляет число в меньшую сторону. Поскольку время является дробной частью целого дня в понимании Excel, то фактически эти часы будут отсечены. То же касается и других элементов даты, числа, месяца, которые представлены, как дробная часть.

Кстати, можно использовать формулу =ЧИСТРАБДНИ() для того, чтобы определить количество рабочих дней между двумя датами.

Разница дат в полных месяцах

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

То есть, формула следующая.

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно.

Разница в днях без учета лет

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

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

Быстрое выделение различающихся значений

Это очень просто. Если вам необходимо понять, есть ли отличающиеся значения в документе Эксель, вам необходимо найти кнопку «Найти и выделить», расположенную на вкладке «Главная». После этого в диалоговом окне «Выделить группу ячеек» в качестве критерия выбираем «отличия по строкам».

2 3

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

Сравнение 2 дат с помощью условного форматирования

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

Чтобы воспользоваться этой опцией, необходимо перейти на вкладку «Главная», и там найти соответствующую большую кнопку. Появится список, в котором нас интересует пункт «Управление правилами». После того, как мы выберем опцию «Диспетчер правил условного форматирования», нам необходимо создать правило с помощью специальной кнопки, которая располагается там же.

В качестве правила условного форматирования необходимо использовать формулу. После этого записываем формулу (важно, чтобы результат получался истинным), стиль форматирования и нажимаем на кнопку «ОК».

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

Сравнение 2 столбцов на предмет совпадения

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

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

Сама последовательность действий следующая:

Переходим на вкладку «Формулы». Там нужно найти кнопку «Присвоит имя». Она находится в самом верху панели инструментов непосредственно под заголовком самой вкладки.
Откроется окно, в котором нам нужно задать имя для каждого из диапазонов. Допустим, нам нужно назвать первый диапазон «Таблица 1».
После этого выбираем диапазон, который нам нужно так назвать. Для этого нажимаем на кнопку, расположенную справа от поля, подписанного как «Диапазон», выделить нужный набор ячеек и нажать на кнопку «ОК».
Действия со вторым списком аналогичны. Единственное исключение – нужно дать ему другое имя. Ну и естественно, задать правильные адреса этого диапазона.

Рекомендация. Воспользуйтесь полем имен для того, чтобы дать диапазону нужное имя значительно быстрее.

Найти его вовсе несложно. После того, как мы выделим подходящий набор ячеек, слева от строки формул нужно нажать на поле, показанное на скриншоте. После этого вводим имя и нажимаем на кнопку «Ввод».

Для сравнения двух колонок нам нужно воспользоваться уже знакомым инструментом «Условное форматирование». Приблизительная последовательность действий будет следующей. Если говорить о конкретных шагах, то они будут сильно зависеть от того, какую конкретную задачу необходимо выполнить пользователю.

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

Нами была применена функция СЧЕТЕСЛИ , которая осуществляет проверку списка на предмет соответствия определенному критерию, после чего выдает значение «ИСТИНА» или «ЛОЖЬ». Если была возвращена «ИСТИНА», то соответствующие ячейки окрашиваются в цвета, заданные пользователем до использования условного форматирования.

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

Формула для сравнения 2 дат

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

Таких нюансов может быть еще огромное количество. Но все эти тонкости постигаются в процессе работы с электронными таблицами. К сожалению, даже одной большой статьи недостаточно, чтобы раскрыть все аспекты сравнения дат. Тем не менее, это не такая сложная задача, как может показаться на первый взгляд. И мы только что в этом убедились. Успехов.

Одной из самых востребованных групп операторов при работе с таблицами Excel являются функции даты и времени. Именно с их помощью можно проводить различные манипуляции с временными данными. Дата и время зачастую проставляется при оформлении различных журналов событий в Экселе. Проводить обработку таких данных – это главная задача вышеуказанных операторов. Давайте разберемся, где можно найти эту группу функций в интерфейсе программы, и как работать с самыми востребованными формулами данного блока.

Работа с функциями даты и времени

Группа функций даты и времени отвечает за обработку данных, представленных в формате даты или времени. В настоящее время в Excel насчитывается более 20 операторов, которые входят в данный блок формул. С выходом новых версий Excel их численность постоянно увеличивается.

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

Для введения формулы через Мастер функций выделите ячейку, где будет выводиться результат, а затем сделайте щелчок по кнопке «Вставить функцию». Расположена она слева от строки формул.

Кроме того, Мастер функций можно активировать, выделив ячейку на листе и нажав комбинацию клавиш Shift+F3. Существует ещё возможность перехода во вкладку «Формулы», где на ленте в группе настроек инструментов «Библиотека функций» следует щелкнуть по кнопке «Вставить функцию».

Имеется возможность перемещения к окну аргументов конкретной формулы из группы «Дата и время» без активации главного окна Мастера функций. Для этого выполняем перемещение во вкладку «Формулы». Щёлкаем по кнопке «Дата и время». Она размещена на ленте в группе инструментов «Библиотека функций». Активируется список доступных операторов в данной категории. Выбираем тот, который нужен для выполнения поставленной задачи. После этого происходит перемещение в окно аргументов.

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

Его аргументами являются «Год», «Месяц» и «День». Особенностью обработки данных является то, что функция работает только с временным отрезком не ранее 1900 года. Поэтому, если в качестве аргумента в поле «Год» задать, например, 1898 год, то оператор выведет в ячейку некорректное значение. Естественно, что в качестве аргументов «Месяц» и «День» выступают числа соответственно от 1 до 12 и от 1 до 31. В качестве аргументов могут выступать и ссылки на ячейки, где содержатся соответствующие данные.

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

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

РАЗНДАТ

Своего рода уникальной функцией является оператор РАЗНДАТ. Он вычисляет разность между двумя датами. Его особенность состоит в том, что этого оператора нет в перечне формул Мастера функций, а значит, его значения всегда приходится вводить не через графический интерфейс, а вручную, придерживаясь следующего синтаксиса:

Из контекста понятно, что в качестве аргументов «Начальная дата» и «Конечная дата» выступают даты, разницу между которыми нужно вычислить. А вот в качестве аргумента «Единица» выступает конкретная единица измерения этой разности:

Год (y);
Месяц (m);
День (d);
Разница в месяцах (YM);
Разница в днях без учета годов (YD);
Разница в днях без учета месяцев и годов (MD).

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

Синтаксис выглядит таким образом:

ТДАТА

Оператор ТДАТА интересен тем, что не имеет аргументов. Он в ячейку выводит текущую дату и время, установленные на компьютере. Нужно отметить, что это значение не будет обновляться автоматически. Оно останется фиксированным на момент создания функции до момента её перерасчета. Для перерасчета достаточно выделить ячейку, содержащую функцию, установить курсор в строке формул и кликнуть по кнопке Enter на клавиатуре. Кроме того, периодический пересчет документа можно включить в его настройках. Синтаксис ТДАТА такой:

СЕГОДНЯ

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

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

ВРЕМЯ

Основной задачей функции ВРЕМЯ является вывод в заданную ячейку указанного посредством аргументов времени. Аргументами этой функции являются часы, минуты и секунды. Они могут быть заданы, как в виде числовых значений, так и в виде ссылок, указывающих на ячейки, в которых хранятся эти значения. Эта функция очень похожа на оператор ДАТА, только в отличии от него выводит заданные показатели времени. Величина аргумента «Часы» может задаваться в диапазоне от 0 до 23, а аргументов минуты и секунды – от 0 до 59. Синтаксис такой:

Кроме того, близкими к этому оператору можно назвать отдельные функции ЧАС, МИНУТЫ и СЕКУНДЫ. Они выводят на экран величину соответствующего названию показателя времени, который задается единственным одноименным аргументом.

ДАТАЗНАЧ

Функция ДАТАЗНАЧ очень специфическая. Она предназначена не для людей, а для программы. Её задачей является преобразование записи даты в обычном виде в единое числовое выражение, доступное для вычислений в Excel. Единственным аргументом данной функции выступает дата как текст. Причем, как и в случае с аргументом ДАТА, корректно обрабатываются только значения после 1900 года. Синтаксис имеет такой вид:

ДЕНЬНЕД

НОМНЕДЕЛИ

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

ДОЛЯГОДА

Оператор ДОЛЯГОДА производит долевой расчет отрезка года, заключенного между двумя датами ко всему году. Аргументами данной функции являются эти две даты, являющиеся границами периода. Кроме того, у данной функции имеется необязательный аргумент «Базис». В нем указывается способ вычисления дня. По умолчанию, если никакое значение не задано, берется американский способ расчета. В большинстве случаев он как раз и подходит, так что чаще всего этот аргумент заполнять вообще не нужно. Синтаксис принимает такой вид:

Мы прошлись только по основным операторам, составляющим группу функций «Дата и время» в Экселе. Кроме того, существует ещё более десятка других операторов этой же группы. Как видим, даже описанные нами функции способны в значительной мере облегчить пользователям работу со значениями таких форматов, как дата и время. Данные элементы позволяют автоматизировать некоторые расчеты. Например, по введению текущей даты или времени в указанную ячейку. Без овладения управлением данными функциями нельзя говорить о хорошем знании программы Excel.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Мой предыдущий пост EXCEL для чайников.1.ВПР содержал несколько грубых педагогических ошибок. Почитав комментарии и сделав выводы, я решил двигаться дальше, побольше, так сказать, разъясняя. Готовьтесь, пост будет еще длиннее.

Сегодня я расскажу вам о времени. Ученые и философы испокон веков спорят о его происхождении, и даже о его существовании. В Excel оно точно есть и работать с ним можно и нужно. Итак, что же такое время в Excel? Возьмем число 42997,635216. Что оно может означать? Человек, работающий с датами в Excel, сразу поймет подвох. Дело в том что это дата и время, в Excel, взятые на момент написания поста функцией =ТДАТА() в формате обычного числа. Про форматы поговорим отдельно, сначала нужно разобраться с представлением дат и времени: 42997 – это количество дней, начиная с 1 января 1900 года, (так что если вы историк то работать с датами до 1900 года придется как обычным текстом, без вычислительных выкрутасов), Стоит так же отметить, что сейчас работать с датами можно вплоть до 31 декабря 9999 г. (тут писатели фантасты печально вздохнули и полезли за калькуляторами). Дробная часть 0,635216 это время от целых суток, то есть 1 – это 24 часа. Давайте проверим за Excel, все ли правильно он посчитал: 0,635216*24=15,245184 (15 это часы); 0,245184*60=14,71104 (14 это минуты); 0,71104*60=42,6624 (43 это секунды, округляем до целого). Теперь переводим ячейку с числом 42992,57046 в формат даты и времени и получаем 19.09.17 15:14:43, хотя функция =ТДАТА() уже изрядно убежала за время нашего расчета. Поверьте, нет смысла проверять за Excel. Не нужно тратить на это драгоценное время, он все считает правильно, ошибка может быть только по другую сторону монитора. Многим это покажется смешным, но у меня есть знакомые, которые проверяют Excel на калькуляторе.

Понимание того что целая часть - это дни, а дробное - это часы, минуты, и секунды, очень важно. Функция =ТДАТА() прекрасна и опасна. Ведь она выполняет расчеты относительно текущего времени, и, если это не нужно и об этом забыть, цифры в таблице поплывут. Они будут плыть пока мы не закрепим дату. Нужно взять ячейку с формулой, войти в режим редактирования и нажать F9, либо копировать её и вставить обратно в туже ячейку как значение. Ход времени в таблице остановится, данные перестанут обновляться.

ТДАТА( ), СЕГОДНЯ( ) – текущее дата и время в первом случае, и дата без времени во втором.

ВРЕМЗНАЧ(“Текст”), ДАТАЗНАЧ(“Текст”) – переводит время либо дату из текста в числовой формат;

ДЕНЬНЕД(Дата, Тип) – выдает номер дня недели (наш тип недели 2, американской 1).

КОНМЕСЯЦА(Дата; Число_Месяцев) – это дата последнего дня месяца со смещением на нужное количество месяцев

ДАТАМЕС(Дата; Число_месяцев) – передвигает эту же дату на нужное количество месяцев вперед или назад

НОМНЕДЕЛИ(Дата; Тип) – номер недели с начала года (тип как в ДЕНЬНЕД)

РАБДЕНЬ(Дата; Количество; Праздники) – дата, которая будет или была через заданное количество дней (учитывая или нет праздники)Праздники задаются диапазоном ячеек

РАБДЕНЬ.МЕЖД(Дата; Количество; Выходной; Праздники) – то же самое, но с расширенной настройкой выходных данных. Можно задать строкой где 0-это рабочий день, 1-это выходной, на пример нормальная рабочая неделя выглядит так “0000011”

ЧИСТРАБДНИ (Дата1; Дата2; Праздники) – возвращает количество рабочих дней между 2 датами (с праздниками или без них)

ЧИСТРАБДНИ.МЕЖД (Дата1; Дата2; Выходной; Праздники) – то же самое, но с произвольным выбором выходных дней. (см. РАБДЕНЬ.МЕЖД)

ДНЕЙ360(Дата1; Дата2) – “Функция ДНЕЙ360 возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если система бухгалтерского учета основана на двенадцати 30-дневных месяцах.” © - взял из справки, в бухгалтерии не силен, ничего добавить не могу, кроме того, что это как-то связано с расчетом равномерности платежей в течении года. В общем, эти бухгалтера даже в году умудрились спереть 5 дней.

ДОЛЯГОДА(Дата1; Дата2; Базис) – это доля года между двух дат. Базис равен 1, если хотите считать по фактическим датам. В противном случае данное значение варьируется от 0 до 3, выбирайте то, что нужно, согласно пояснениям, содержащимся в справке.

Теперь давайте разберемся, как это работает. В столбце А я напишу формулу, а в столбце В, С, D я напишу результат этой формулы в разных форматах, в столбце E напишу комментарии.

Еще раз обращаю внимание на то, что значения в столбцах B,C,D равны друг другу. Рассмотрим небольшой пример работы с рабочими днями. На пример давайте представим, что наша Госдума хочет сделать в 2018 году пятницу или понедельник выходным днем. Но для этого нужно убрать отпуск и праздничные дни чтобы компенсировать недостаток рабочего времени. Сколько же рабочих дней у нас получится? Находим праздничные дни в 2018 году (как оказалось их 14), вбиваем эти дни в таблицу. Я не учитывал переносы праздников с субботы на понедельник, так как в этом случае количество рабочих дней не меняется.

Понедельников у нас в 2018 году на 1 день больше чем пятниц, так что результат разный. Напоследок, давайте сделаем какой-нибудь пример с использованием некоторых приемов. Допустим Вы начальник кадровой службы, у вас есть дата и время прихода и ухода сотрудников на работу. Нужно посчитать общее время, проведенное на работе и посмотреть, нет ли нарушений в распорядке трудового дня. Также есть начало и конец рабочего дня, которые задаются значениями в ячейках.

Время на работе (ч) =(C2-B2)*24 – тут мы вычитаем дату прихода из даты ухода, из суток переводим в часы, все просто.

Время на работе в рабочие дни (ч) =(ЧИСТРАБДНИ(B2;B2)*(C2-B2))*24 тут мы учитываем, был ли день рабочий. Я обнаружил, что если применить ЧИСТРАБДНИ с указанием одного дня эта функция в случае рабочего дня выдаст 1 и в случае выходного - 0, далее все как в формуле выше.

Опоздание (мин) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);ЕСЛИ(B2-ОКРУГЛВНИЗ(B2;0)<$K$1;"";ОКРУГЛ(((B2-ОКРУГЛВНИЗ(B2;0))-$K$1)*24*60;0));""). Также, как и выше, мы проверяем рабочий ли у нас день. Затем (это мое любимое) вычисляем время прихода сотрудника, без учета даты. Для этого я отнимаю из даты со временем значение той же даты со временем округленное вниз до целого значения с помощью функции ОКРУГЛВНИЗ. Выражение B2-ОКРУГЛВНИЗ(B2;0) у нас будет иметь значение 8:42 в формате времени, то есть время прихода сотрудника. В принципе мы могли бы написать =ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)), это аналогичное решение, которое собирает время из значений часов, минут и секунд, но первое решение мне нравится больше. Затем сравниваем это время с временем начала рабочего дня, если оно меньше - оставляем ячейку пустой (“”), если же больше - считаем что сотрудник опоздал и высчитываем опоздание в минутах: из времени фактического прихода отнимаем время начала рабочего дня и умножаем на 24 и на 60, чтобы получить минуты, затем округляем полученный результат до целого значения. В случае же если день выходной, то логическое условие функции ЕСЛИ не выполняется и ячейка остается пустая.

Ранний уход (мин) =ЕСЛИ(ЧИСТРАБДНИ(C2;C2);ЕСЛИ(C2-ОКРУГЛВНИЗ(C2;0)>$K$2;"";ОКРУГЛ(($K$2-(C2-ОКРУГЛВНИЗ(C2;0)))*24*60;0));""). Тут все аналогично предыдущему, за исключением того что учитывается время ухода, которое должно быть больше времени окончания рабочего дня.

Вот и все что я хотел рассказать про время, пост получился длинноватым. Надеюсь, Вы меня поняли. Буду думать, о чем рассказать в следующий раз. Помните, сначала Вы работаете в Excel, потом Excel работает за Вас!

Excel

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

Рассчитать разницу во времени в Excel

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

Просто рассмотрите пример данных, как показано ниже,

Итак, как уже говорилось, если мы просто попытаемся вычесть «Время окончания» из «Время начала», вы столкнетесь с двумя проблемами и посмотрим, что это.

Мы можем решить первую проблему, изменив формат чисел, выполнив следующие простые шаги

Выберите ячейки, которые имеют результат (общее количество часов в нашем случае) и нажмите ‘CTRL + 1’, чтобы открыть диалоговое окно форматирования ячеек.
Нажмите на «Custom» и в поле «type» введите «h: mm» и нажмите «Ok».

E14-E13 + (E13> E14)

Теперь вместо нажатия клавиши Enter просто нажмите «CTRL + Enter», чтобы результат был применен ко всем выделенным ячейкам за один снимок. Таким образом, мы можем избежать изменения формулы для каждой ячейки.

Это простой, удобный и совершенный способ подсчета разницы во времени в Microsoft Excel.

Читайте также: