Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах
Предположим, вы работаете в пенсионном фонде и хотите определить трудовой стаж в количестве лет, дней, месяцев и так далее. Это можно сделать с помощью функции РАЗНДАТ.
Ее синтаксис элементарный.
РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)
Единственный из этих аргументов, который действительно может вызвать вопросы у неподготовленного читателя – это способ измерения. С его помощью мы задаем, в каких единицах будет измеряться разница между датами. Это может быть количество месяцев, лет или количество месяцев без учета лет. Еще один вариант – это количество дней без учета месяцев и лет.
Важно учесть, что в некоторых случаях эта функция может вернуть неправильное значение. Это бывает, когда день первой даты больше, чем день второй
Такая ситуация может быть, например, когда нужно рассчитать количество дней между двумя датами, первая из которых происходила в конце месяца, а другая – в начале следующего.
Пример №1. Подсчет количества лет между двумя датами
На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.
Разница дат в полных месяцах
Чтобы определить количество полных месяцев, которое прошло с одной даты до другой, необходимо воспользоваться такой же самой формулой, которая была приведена выше, только третий аргумент должен содержать аргумент m.
То есть, формула следующая.
=РАЗНДАТ(A2;B2;”m”)
Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно.
Разница в днях без учета лет
Эта формула также может использоваться для определения количества дней между двумя датами, но при этом не учитывать количество лет, которое прошло между этими двумя временными точками. Эта возможность есть, но пользоваться ею настоятельно не рекомендуется, потому что искажений в этом случае может быть еще больше.
1
Эта формула вернет правильный результат только тогда, когда в компьютере показано правильное время. Конечно, в большинстве современных операционных системах дата и время определяется автоматически, но иногда случаются сбои. Поэтому перед тем, как подсчитывать разницу в днях без учета лет, нужно убедиться, что на компьютере установлена правильная дата и время. Особенно если электронная таблица пересылалась другому человеку.
Количество рабочих дней между двумя датами
Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):
Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).
Сдвиг даты на заданное количество дней
Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.
Сдвиг даты на заданное количество рабочих дней
Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ(NETWORKDAYS) описанной выше.
Вычисление дня недели
Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.
Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).
Вычисление временных интервалов
Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические операции, как и с датой — сложение, вычитание и т.д.
Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:
Как рассчитать разницу во времени между двумя датами или временем?
Если у вас есть два списка времени, такие как столбец A и столбец B (время в столбце A раньше, чем в столбце B в той же строке), как показано на скриншоте ниже, и вы не можете получить правильное время, отличное от более позднего времени, вычитая ранний. Итак, теперь я говорю о методах расчета разницы во времени в Excel.
Вычислить разницу во времени между двумя временами в Excel
В нашем случае мы предполагаем, что в двух столбцах есть только часы, минуты и секунды, а время в столбце A всегда раньше, чем в столбце B той же строки.
1. Выберите пустую ячейку, в данном случае я выбираю ячейку C2, введите эту формулу. = B2-A2 + (A2> B2) (время в ячейке A2 раньше, чем в ячейке B2, вы можете изменить их по своему усмотрению), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон, необходимый для применения этой формулы. Смотрите скриншот:
2. Выберите диапазон результатов, в данном случае выберите диапазон результатов C2: C6 и щелкните правой кнопкой мыши> Форматы ячеек в контекстном меню. Смотрите скриншот:
3. в Формат ячеек диалоговое окно, нажмите Число вкладка> Продолжительность , и выберите нужный формат времени в правом разделе, нажмите OK. Смотрите скриншот:
Затем вы можете увидеть, что разница во времени была рассчитана, как показано ниже:
Рассчитать разницу во времени между двумя датами в Excel
Если в столбцах указаны не только часы, минуты и секунды, но и даты, вы можете сделать следующее:
1. Выберите пустую ячейку, в данном случае я выбираю ячейку C2, введите эту формулу. = B2-A2 (время в ячейке A2 раньше, чем в ячейке B2, вы можете изменить их по своему усмотрению), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон, необходимый для применения этой формулы. Смотрите скриншот:
2. Выберите диапазон результатов, в данном случае выберите диапазон C2: C6 и щелкните правой кнопкой мыши> Форматы ячеек в контекстном меню. Смотрите скриншот:
3. в Формат ячеек диалоговое окно, нажмите Число вкладка> Продолжительность И выберите 37: 30: 55 из Тип разделом, щелкните OK. Смотрите скриншот:
Затем вы можете увидеть, что разница во времени была рассчитана, как показано ниже:
Рассчитать разницу во времени между двумя датами с помощью Kutools for Excel
Если вы хотите рассчитать разницу во времени и отобразить рассчитанную разницу во времени в виде слов (3 часа 5 минут 12 секунд), вы можете использовать Помощник по дате и времени полезности Kutools for Excel.
| Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу. |
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейку, в которой будет размещена разница во времени, нажмите Кутулс > Формула Помощник > Помощник по дате и времени, см. снимок экрана:
2. В появившемся диалоговом окне отметьте разница флажок, затем выберите два раза в Ввод аргументов текстовых полей отдельно, выберите нужный тип отображения в раскрывающемся списке Тип результата выводасм. снимок экрана:
3. Нажмите OK, затем отобразится первый результат, перетащите маркер автозаполнения по ячейкам, чтобы вычислить всю разницу во времени.
Использование функций при работе с датами и временем
При работе с датами и временем пользователь может использовать более 20 самых разнообразных функций. И хотя кому-то этого количества может быть слишком много, все они могут использоваться для достижения определенных целей.
Чтобы получить доступ ко всем возможным функциям, необходимо перейти в категорию «Дата и время» группы «Библиотека функций». Мы же рассмотрим только некоторые основные функции, дающие возможность извлекать разные параметры из дат и времени.
ГОД()
Дает возможность получить год, который соответствует определенной дате. Как вы уже знаете, это значение может быть в пределах от 1900 до 9999.
В ячейке 1 видно дату, представленную в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это тот формат, который мы создали ранее. Давайте приведем в качестве примера формулу, которая определяет, сколько лет прошло между двумя датами.
При этом если более внимательно посмотреть, то окажется, что функция не вычислила полностью правильного результата. Причина кроется в том, что она использует только даты в своих вычислениях.
МЕСЯЦ()
С помощью этой функции можно выделить номер месяца, соответствующий определенной дате. Возвращает результат, колеблющийся в пределах от 1 до 12. Это число в свою очередь соответствует номеру месяца.
ДЕНЬ()
Аналогично предыдущим функциям, эта выдает номер дня, в определенной дате. Результат вычислений может колебаться от 1 до 31.
МИНУТЫ()
Функция, возвращающая количество минут в определенной ячейке. Возможные значения, которые возвращаются – от 0 до 59.
ДЕНЬНЕД()
С помощью этой функции можно узнать номер дня недели, который используется в этой дате. Возможные значения от 1 до 7, но стоит учитывать, что отсчет начинается с воскресенья, а не понедельника, как у нас принято.
При этом с помощью второго аргумента эта функция позволяет настроить формат. Например, если передать значение 2 в качестве второго параметра, то можно настроить формат, при котором число 1 означает понедельник, а не воскресенье. Это значительно удобнее для отечественного пользователя.
Если во втором аргументе написать 2, то в нашем случае функция вернет значение 6, что соответствует субботе.
СЕГОДНЯ()
Эта функция очень проста: чтобы она работала, не требуется вводить никаких аргументов. Ею возвращается порядковый номер даты, которая выставлена на компьютере. Если ее применить к ячейке, для которой выставлен формат Общий, то автоматически он будет сконвертирован в формат «Дата».
ТДАТА()
Эта функция также не требует ввода аргументов. Действует таким же образом, как и предыдущая, только с датой и временем. Используется, если надо вставить в ячейку текущую дату и время, которые выставлены в компьютере. И точно так же, как и в предыдущей функции, при применении этой, ячейка автоматически конвертируется в формат даты и времени при условии, что до этого был выставлен формат «Общий».
Как предыдущая, так и эта функция при каждом пересчете листа автоматически изменяются, что дает возможность отображать самое актуальное время и дату.
Например, такая формула может определить нынешнее время.
=ТДАТА()-СЕГОДНЯ()
В этом случае формула определит долю суток в десятичном формате. Правда, придется к ячейке, в которую записывается формула, применить формат времени, если нужно отобразить именно время, а не число.
ДАТА()
Эта функция имеет три аргумента, каждый из которых необходимо обязательно ввести. После расчетов этой функцией возвращается порядковый номер даты. Ячейка автоматически конвертируется в формат «Дата», если до этого у нее был «Общий» формат.
Аргумент «День» или «Месяц» можно делать как положительным, так и отрицательным. В первом случае дата увеличивается, а во втором – уменьшается.
Также можно использовать математические операции в аргументах функции ДАТА. Например, эта формула позволяет добавить 1 год 5 месяцев и 17 дней к дате, указанной в ячейке A1.
А такая формула дает возможность превратить текстовую строку в полноценную рабочую дату, которая может использоваться и в других функциях.
ВРЕМЯ()
Точно так же, как и функция ДАТА(), в этой функции есть три обязательных параметра – часы, минуты и секунды. После того, как ее использовать, в результирующей ячейке появится десятичное число, но сама ячейка будет отформатирована в формат «Время», если до этого у нее был формат «Общий».
По своему принципу работы у функции ВРЕМЯ() и ДАТА() очень много чего схожего. Поэтому особого внимания на ней акцентировать нет смысла.
Важно учесть, что эта функция не способна вернуть время, которое больше 23:59:59. Если получится больший, чем этот, результат, функция автоматически обнуляется
Функции ДАТА() и ВРЕМЯ() могут применяться вместе.
На этом скриншоте ячейка D1, в которой использовались обе эти функции, имеет формат даты и времени.
Как Excel обрабатывает дату и время?
Как я уже упоминал, даты и время хранятся в виде чисел в ячейке Excel. Целое число представляет собой полный день, а десятичная часть числа будет представлять часть дня (которая может быть преобразована в значения часов, минут и секунд).
Например, значение 1 представляет 01 января 1900 года в Excel, что является отправной точкой, с которой Excel начинает считать даты.
Таким образом, 2 будет означать 2 января 1990 года, 3 будет означать 3 января 1900 года и так далее, а 44197 будет означать 1 января 2021 года.
Если в этих числах есть какие-либо цифры после десятичной точки, Excel будет рассматривать их как часть дня, и их можно преобразовать в часы, минуты и секунды.
Например, 44197,5 будет означать 1 января 2021 года 12:00:00 PM.
Поэтому, если вы работаете со значениями времени в Excel, вы, по сути, будете работать с десятичной частью числа.
И Excel дает вам гибкость для преобразования этой десятичной части в различные форматы, такие как только часы, только минуты, только секунды или комбинацию часов, минут и секунд.
Теперь, когда вы понимаете, как хранится время в Excel, давайте рассмотрим несколько примеров того, как рассчитать разницу во времени между двумя разными датами или временем в Excel.
Пояснение
Сложность подсчета количества часов между двумя моментами времени следует из времени, которое пересекает полночь. Это связано с тем, что время, которое пересекает полночь, часто имеет время начала позже, чем время окончания (т.е. начало в 21:00, окончание в 6:00). В этой статье представлены несколько формул решений в зависимости от ситуации.
Простой расчет продолжительности
Когда время начала и время окончания совпадают с одним и тем же днем, вычислить продолжительность в часах несложно. Например, с временем начала 9:00 и временем окончания 17:00 вы можете просто использовать эту формулу:
= конец-начало
= 17:00–8:00
= 0,375-0,708 = 0,333
Результат: 8 часов
|
1 |
=конец-начало =,375-,708=,333 Результат:8часов |
Чтобы увидеть результат в часах и минутах, примените следующий формат времени:
h:mm
| 1 | hmm |
Однако, когда время пересекает границу суток (полночь), все может усложниться.
Как Excel отслеживает время
В Excel один день равен 1, что соответствует 24 часам. Это означает, что время и часы являются дробными значениями от 1, как показано в таблице ниже:
| Часы | Время | Доля | Значение |
|---|---|---|---|
| 3 | 3:00 | 3/24 | 0,125 |
| 6 | 6:00 | 6/24 | 0,25 |
| 4 | 4:00 | 4/24 | 0,167 |
| 8 | 8:00 | 8/24 | 0,333 |
| 12 | 12:00 | 12/24 | 0,5 |
| 18 | 18:00 | 18/24 | 0,75 |
| 21 | 21:00 | 21/24 | 0,875 |
Когда наступает полночь
Вычислить прошедшее время сложнее, если время пересекает границу суток (полночь). Например, если в один день время начала — 22:00, а время окончания — 5:00 утра следующего дня, то время окончания на самом деле меньше, чем время начала, и приведенная выше формула вернет отрицательное значение, а Excel отобразит строку хеш-символов (########).
Чтобы исправить эту проблему, вы можете использовать эту формулу для времени, которое проходит границу суток:
=1-начало+конец
| 1 | =1-начало+конец |
Вычитая время начала из 1, вы получаете количество времени в первый день. Это время вы можете просто добавить ко времени 2-го дня, которое совпадает с временем окончания. Эта формула не будет работать для случаев, которые происходят в один и тот же день, поэтому нам нужно использовать функцию ЕСЛИ следующим образом:
=ЕСЛИ(конец>начало; конец-начало; 1-начало+конец)
| 1 | =ЕСЛИ(конец>начало;конец-начало;1-начало+конец) |
Когда оба времени относятся к одному и тому же дню, конец больше, чем время начала, то используется простая формула. Но когда время пересекает границу суток, используется вторая формула.
Альтернатива c функцией ОСТАТ
Функция ОСТАТ представляет собой элегантный способ упростить приведенную выше формулу. Используя функцию ОСТАТ с делителем 1, мы можем создать формулу для обработки обеих ситуаций:
=ОСТАТ(конец-начало;1)
| 1 | =ОСТАТ(конец-начало;1) |
Эта формула учитывает отрицательное время, используя функцию ОСТАТ, чтобы «перевернуть» отрицательное значение на требуемое положительное значение. Поскольку эта формула обрабатывает время в один и тот же день и время, после полуночи, то нам не нужен условный оператор ЕСЛИ.Примечание: ни одна из приведенных выше формул не будет обрабатывать длительность более 24 часов.
Если вам это нужно, см. параметр «дата + время» ниже.
Упрощение с помощью даты + времени
Вы можете просто решить задачу радикального вычисления прошедшего времени, используя значения, содержащие как дату, так и время. Чтобы ввести вместе дату и время, используйте единый интервал между датой и временем.
Например. Если вы отформатируете эту дату в основном формате, вы увидите такое значение:
43922.41667
| 1 | 43922.41667 |
Числа справа от десятичной дроби представляют собой компонент времени. Если у вас есть значения «дата и время», вы можете использовать базовую формулу для расчета прошедшего времени. На приведенном ниже рисунке начальное и конечное значения содержат как дату, так и время, а формула проста:
=C5-B5 ‘конец-начало
| 1 | =C5-B5’конец-начало |
Результат отформатирован в пользовательском формате: для отображения прошедших часов. Эта формула правильно рассчитает часы между двумя временными метками за один день или за несколько дней.
Сложение / вычитание времени в Excel
До сих пор мы видели примеры, в которых у нас было время начала и время окончания, и нам нужно было найти разницу во времени.
Excel также позволяет легко добавлять или вычитать фиксированное значение времени из существующего значения даты и времени.
Например, предположим, что у вас есть список задач в очереди, где каждая задача занимает определенное время, и вы хотите знать, когда каждая задача закончится.
В таком случае вы можете легко добавить время, которое займет каждая задача, к времени начала, чтобы узнать, в какое время задача должна быть завершена.
Поскольку Excel хранит значения даты и времени в виде чисел, вы должны убедиться, что время, которое вы пытаетесь добавить, соответствует формату, которому уже следует Excel.
Например, если вы добавите 1 к дате в Excel, это даст вам следующую дату. Это потому, что 1 представляет в Excel целый день (который равен 24 часам).
Поэтому, если вы хотите добавить 1 час к существующему значению времени, вы не можете просто добавить 1 к нему. вы должны убедиться, что вы преобразовали это значение часа в десятичную часть, представляющую один час. то же самое касается добавления минут и секунд.
Использование функции ВРЕМЯ
Функция времени в Excel принимает значение часа, минут и секунд и преобразует их в десятичное число, представляющее это время.
Например, если я хочу добавить 4 часа к существующему времени, я могу использовать следующую формулу:
= Время начала + ВРЕМЯ (4,0,0)
Это полезно, если вы знаете, сколько часов, минут и секунд вы хотите добавить к существующему времени, и просто используете функцию ВРЕМЯ, не беспокоясь о правильном преобразовании времени в десятичное значение.
Также обратите внимание, что функция ВРЕМЯ будет учитывать только целую часть введенного вами значения часа, минуты и секунды. Например, если я использую 5,5 часов в функции ВРЕМЯ, это добавит только пять часов и проигнорирует десятичную часть
Также обратите внимание, что функция ВРЕМЯ может добавлять только значения менее 24 часов. Если значение вашего часа больше 24, это даст вам неверный результат
То же самое касается минут и второй части, где функция будет учитывать только значения, которые меньше 60 минут и 60 секунд.
Так же, как я добавил время с помощью функции ВРЕМЯ, вы также можете вычесть время. Просто измените знак + на отрицательный в приведенных выше формулах.
Использование базовой арифметики
Когда функция времени проста и удобна в использовании, она имеет несколько ограничений (как описано выше).
Если вам нужен больший контроль, вы можете использовать арифметический метод, о котором я расскажу здесь.
Идея проста — преобразуйте значение времени в десятичное значение, которое представляет часть дня, а затем вы можете добавить его к любому значению времени в Excel.
Например, если вы хотите добавить 24 часа к существующему значению времени, вы можете использовать следующую формулу:
= Start_time + 24/24
Это просто означает, что я добавляю один день к существующей временной стоимости.
Теперь, применяя ту же концепцию, предположим, что вы хотите добавить 30 часов к значению времени, вы можете использовать следующую формулу:
= Start_time + 30/24
Вышеупомянутая формула делает то же самое, где целая часть (30/24) будет представлять общее количество дней во времени, которое вы хотите добавить, а десятичная часть будет представлять часы / минуты / секунды.
Точно так же, если у вас есть определенное количество минут, которое вы хотите добавить к значению времени, вы можете использовать следующую формулу:
= Start_time + (добавляемые минуты) / 24 * 60
И если у вас есть количество секунд, которое вы хотите добавить, вы можете использовать следующую формулу:
= Start_time + (минуты для добавления) / 24 * 60 * 60
Хотя этот метод не так прост, как использование функции времени, я считаю его намного лучше, потому что он работает во всех ситуациях и следует той же концепции. в отличие от функции времени, вам не нужно беспокоиться о том, будет ли время, которое вы хотите добавить, меньше 24 часов или больше 24 часов
Вы можете следовать той же концепции, вычитая время. Просто измените знак + на отрицательный в формулах выше.
Как рассчитать разницу во времени в Excel
Excel
Microsoft Excel можно использовать для расчетов бюджета, налоговых расчетов и многих других целей. Вы можете использовать его в качестве другой формы калькулятора благодаря функциям, которые он нам предоставляет. Вы даже можете использовать Excel для расчета разницы между двумя значениями. Это помогает рассчитать количество часов, которые сотрудник проработал между заданным временем начала и окончания. С помощью Excel можно вести полный учет рабочего времени сотрудника, что помогает нам рассчитывать заработную плату, которая будет выплачиваться за сотрудника.
Как вы думаете, очень легко рассчитать разницу во времени, поскольку это просто вычитание времени окончания из времени начала? Если вы просто сделаете то же самое, вы столкнетесь с двумя проблемами. Давайте посмотрим, кто они, и как мы можем справиться с ними.
Рассчитать разницу во времени в Excel
Трудно посчитать разницу между двумя значениями в Excel. Но с небольшим количеством хитрости мы можем достичь желаемого результата. Просто следуйте этой статье до конца, и там вы сможете отлично рассчитать разницу во времени в Excel. Вот так!
Просто рассмотрите пример данных, как показано ниже,
Итак, как уже говорилось, если мы просто попытаемся вычесть «Время окончания» из «Время начала», вы столкнетесь с двумя проблемами и посмотрим, что это.
Вы можете видеть, что к этому результату добавлено «AM» или «PM», чего мы не ожидаем. Вторая проблема – это показывает ошибки хеша. Поскольку мы выполнили прямое вычитание, в некоторых случаях результат может быть отрицательным, и поскольку время не должно быть отрицательным, Excel отображает его как ошибки хеширования.
Мы можем решить первую проблему, изменив формат чисел, выполнив следующие простые шаги
- Выберите ячейки, которые имеют результат (общее количество часов в нашем случае) и нажмите ‘CTRL + 1’, чтобы открыть диалоговое окно форматирования ячеек.
- Нажмите на «Custom» и в поле «type» введите «h: mm» и нажмите «Ok».
Таким образом, вы можете избавиться от первой проблемы. Это несколько удовлетворительно, но даже если это не полное решение, которое мы ищем. Нам по-прежнему необходимо устранить ошибки хэширования в рассчитанных полных часах. Чтобы решить вторую проблему, вам нужно изменить формулу, которую вы использовали на первом шаге (Время окончания – Время начала, т.е. «E14-E13»). Выберите строку «Всего часов» и нажмите «F2», чтобы изменить формулу. Сделайте формулу следующим образом
E14-E13 + (E13> E14)
Теперь вместо нажатия клавиши Enter просто нажмите «CTRL + Enter», чтобы результат был применен ко всем выделенным ячейкам за один снимок. Таким образом, мы можем избежать изменения формулы для каждой ячейки.
Мы все знаем, что 24-часовая система используется Excel. Основная сложность, используемая здесь для решения хеш-ошибок, – это добавление логической операции. Тем самым мы преодолели отрицательный результат, поскольку «ИСТИНА» обрабатывается как «1», а «ЛОЖЬ» – как «0». Наконец, разница во времени не показывает отрицательные значения, иначе больше нет ошибок хеширования.
Это простой, удобный и совершенный способ подсчета разницы во времени в Microsoft Excel.