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

Обновлено: 24.11.2024

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

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

Как вы знаете, значение времени — это числовое значение, отформатированное как время, но когда вы вычитаете одно значение из другого, оно возвращает десятичное число, и вам нужно снова отформатировать его как время.

Но вот и боль:

Когда вы форматируете это значение результата, оно показывает эти значения в виде часов и минут (начиная с 00:00) вместо фактической разницы в часах и минутах.

Поэтому, если у вас есть разница в 5 часов и 30 минут, это будет отображаться как 05:00. Вам больше не о чем беспокоиться.

1. Получить разницу в часах и минутах

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

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

Например, если разница между временем начала и окончания составляет 4 часа 30 минут, она должна отображаться как 4,5 часа. Вот формула, которую вам нужно использовать.

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

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

Как я уже говорил, Excel хранит дату и время в числах, и когда вы вычитаете время окончания из времени начала, он возвращает десятичное число. И когда вы умножаете это число на 24 (общее количество часов в сутках), оно преобразует эти десятичные дроби в количество часов и минут.

3. Отличайтесь отработанными часами

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

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

Эта формула аналогична формуле, которую мы использовали во втором методе, с той лишь разницей, что здесь мы обернули ее в INT.

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

4. Получите разницу в минутах

Иногда вам нужно отслеживать разницу во времени в минутах, а не в часах, и в этом случае лучше всего использовать приведенную ниже формулу.

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

5. Получите разницу в секундах

Как и в случае с минутами, мы можем получить разницу в секундах. Все, что вам нужно сделать, это использовать следующую формулу.

Здесь 86400 – это общее время в секундах за 24 часа. Когда вы вводите эту формулу, она возвращает разницу во времени начала и окончания в секундах.

6. Вычислить разницу как отрицательное значение

Во всех приведенных выше примерах время окончания больше (позднее) времени начала.

Предположим, у вас есть два значения времени, где время окончания предшествует времени начала. Проще говоря, если время начала — 23:00 дня 1, а время окончания — 5:00 утра дня 2.

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

=IF(конец>начало, конец-начало, 1-начало+конец)

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

В этой формуле у вас есть условие: если время окончания больше времени начала, тогда оно просто вычитает время окончания из времени начала, в противном случае сначала время начала вычитается из 1, а затем добавляется к времени окончания. .

Вычитая время начала из 1, вы получите общее время за день 1, а прибавив его к времени окончания, вы получите общее время дня 1 и дня 2, то есть общую разницу.< /p>

Заключение

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

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

Но вы должны сказать мне одну вещь.

Знаете ли вы какой-либо другой метод, который можно использовать для получения разницы между временем начала и временем окончания?

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

24 мысли

Оставить комментарий Отменить ответ

Вышеупомянутый метод не помог мне больше, чем я знаю
Спасибо

Знаете ли вы, как рассчитать разницу между заданной датой начала и датой окончания, если вы хотите исключить выходные и определенные праздники, кроме как с помощью функции ЧИСТРАБДНИ?

Привет, у меня проблема с вычислением 2 раз. Допустим, я проехал на велосипеде в общей сложности 35 ч 35 мин 35 сек, а цель — 40 ч 0 мин 0 сек. Вычислить разницу как отрицательную, так и положительную, похоже, не получится.
Я попробовал =ТЕКСТ(C42-C43","чч:мм:сс"), где C42 – это общее значение, а C43 – заданная цель.
Excel вообще не распознает это как формулу и говорит, что это проблема с этой формулой?
Я попытался найти решение в сети, но не нашел его.

ПС. Формат ячейки 37:30:55.

Здравствуйте,
что делать, если мне нужно рассчитать ежедневное рабочее время (8) часов, если оно превышает 8 часов, чтобы рассчитать сверхурочное время для трех смен и время с учетом времени с 19:00 до 07:00 как 1,5 ставка и другие часы будут в ставке 1,25

означает
Пример
Exmp1-08:00 до 20:00 8 часов работы 4 часа Overtime (3 часа при 1.25 и 1 час при 1.5)
Exmp2-20:00 до 05: 00 8 часов работы 1 час Сверхурочное (@1.5) (до 07:00 ставка будет 1,5) после 07:00 ставка будет 1,25

15.04.2021 14:44 17.04.2021 13:35 22:51
Я пытаюсь рассчитать время за период более 2 дней, но это дает результаты менее чем за один день

СПАСИБО!! Я прочесывал сеть, пытаясь узнать, как вычислить два раза как число в Excel. Вы спасатель!

Хочется использовать 4-значное число в качестве времени (не вводить двоеточие), чтобы время выглядело так»
1347 2152 (время начала и окончания)
Какие форматы вы бы использовали в соответствии с определением в каждой ячейке? Общие, номер или время (nn:nn)?
Какую формулу вы бы использовали?
Я хотел бы видеть три ячейки вот так:

Здравствуйте, как я могу быстро рассчитать время из отчета, который предоставляет время начала и окончания, как это. Чтобы убедиться, что вы понимаете, первая строка с 6:32:59 до 6:50:24 утра
Начало Конец

63259 65024
65024 84435
84435 90048
90048 102856
102856 104047
104047 111507
111507 113000
123448
1327549
>132758 135942
135942 141500
141500 144414
144414 145926

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

Начало 20.09.2020 19:01:28 Конец 21.09.2020 08:12:36

В рамках этого периода времени мне также необходимо определить количество рабочих часов с 19:00 до 7:00.

У меня немного другая проблема, и я надеюсь, что вы сможете ответить.
На моем листе следующим образом
Столбец C4=Дата начала
Столбец D4=Время начала
Столбец E4=Общий перерыв в имутах
Столбец F4=Дата окончания
>Столбец G4= Время окончания
и, наконец,
Столбец H4= Общее количество рабочих часов
Я пробовал разные комбинации с разных веб-сайтов, но не смог найти решение для столбца H4.
Благодарим за помощь.
Спасибо

Как мне отобразить разницу во времени в часах и минутах вместо часов целыми числами и десятичными дробями, представляющими единицы 100 вместо 60?

Например: я хочу, чтобы в течение 4 с половиной часов отображалось 4,30, а не 4,5? Я знаю, что сложный метод заключается в кропотливом преобразовании, и это долго и неправильно. Хотел узнать, нашли ли вы более простой и подходящий метод. Спасибо!

Вы спасаете жизни. Спасибо! Продолжайте в том же духе!

Сэр, 20 февраля 2020 г.
Как вы могли это сделать? Чудо !
Не могу поверить.
Много, много усилий вы, возможно, предприняли. не так ли?
Большое спасибо.
Заинтересованные в Excel, такие люди, как я, будут очень рады отметить такие советы и рекомендации.
Я буду рад получать от вас больше и заметки и в будущем.
Еще раз спасибо.
Канхаялал Ньюаскар.

Здравствуйте,
Как мне создать вложенную функцию ЕСЛИ для вычисления частей, взятых между двумя разами? Пример: у меня есть 42 дела, собранные с 8:30 до 9:15. Мне нужна формула, чтобы зафиксировать, что было собрано в каждый период времени, и чтобы каждое количество было помещено в правильный час (например, с 8:00 до 9:00 и с 9:00 до 10:00). Есть ли функция, которая позволит мне это сделать? Если да, помогите.

Я испробовал несколько формул, но, как вы видите, дата начала – 22 июня, а дата окончания – 23 июля. Оставшиеся дни должны показывать, что осталось 1 день – показывает 8 вы можете помочь

Привет, Пунит
Как нам получить разницу между комбинацией даты и времени и даты и времени без использования текста в столбцах для отдельной даты и времени.
Для этого примера: 04.04.2019 06:00 – 04.05.2019 19:00

Спасибо!

Как изменить формулу для проверки даты?

например, если я начал вчера в 5 утра и закончил сегодня в 9 утра, формула просто даст 4 часа (если я умножу на 24) вместо 16 часов.

Как рассчитать разницу во времени между двумя днями, т. е. временем начала 31 марта 2019 года в 23:30 и временем окончания 1 апреля 19 года в 02:30? Я хочу получить результат за 3,0 часа?

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

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

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

Добавить время

Предположим, вы хотите узнать, сколько часов и минут потребуется для выполнения двух задач. По вашим оценкам, первая задача займет 6 часов 45 минут, а вторая — 9 часов 30 минут.

Вот один из способов настроить это на листе.

Введите 6:45 в ячейку B2 и введите 9:30 в ячейку B3.

В ячейке B4 введите =B2+B3 и нажмите клавишу ВВОД.

Результат: 16:15 — 16 часов 15 минут — на выполнение двух задач.

Совет. Вы также можете суммировать время, используя функцию автосуммы для суммирования чисел. Выберите ячейку B4, а затем на вкладке Главная выберите Автосумма. Формула будет выглядеть так: =СУММ(B2:B3). Нажмите Enter, чтобы получить тот же результат, 16 часов и 15 минут.

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

Чтобы суммировать более 24 часов:

В ячейке B2 введите 12:45, а в ячейке B3 введите 15:30.

Введите =B2+B3 в ячейку B4 и нажмите клавишу ВВОД.

Результат — 4:15, что не соответствует ожиданиям. Это связано с тем, что время задачи 2 указано в 24-часовом формате. 15:30 равно 3:30.

Чтобы отобразить время более 24 часов, выберите ячейку B4.

На вкладке "Главная" в группе "Ячейки" выберите "Формат", а затем выберите "Формат ячеек".

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В поле "Тип" в верхней части списка форматов введите [h]:mm;@ и нажмите кнопку "ОК".

Обратите внимание на двоеточие после [h] и точку с запятой после mm.

Результат: 28 часов 15 минут. Формат появится в списке Тип в следующий раз, когда он вам понадобится.

Вычесть время

Вот еще один пример. Допустим, вы и ваши друзья знаете время начала и окончания волонтерского проекта и хотите знать, сколько времени вы потратили в целом.

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

В ячейке B2 введите время начала и добавьте "a" для AM или "p" для PM. Затем нажмите Enter.

В ячейке C2 введите время окончания, включая "a" или "p" в зависимости от обстоятельств, а затем нажмите клавишу ВВОД.

Введите другое время начала и окончания для ваших друзей, Джой и Лесли.

В ячейке D2 вычтите время окончания из времени начала, введя формулу =C2-B2, а затем нажмите клавишу ВВОД.

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В списке "Тип" выберите ч:мм (часы и минуты), а затем нажмите кнопку "ОК".

Теперь мы видим, что Ричард работал 3 часа 45 минут.

Чтобы получить результаты для Джой и Лесли, скопируйте формулу, выбрав ячейку D2 и перетащив ее в ячейку D4.

Форматирование в ячейке D2 копируется вместе с формулой.

Чтобы вычесть время, превышающее 24 часа:

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

Выполните следующие действия:

Ссылаясь на приведенный выше пример, выберите ячейку B1 и перетащите ее в ячейку B2, чтобы можно было применить формат к обеим ячейкам одновременно.

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В поле Тип в верхней части списка форматов введите м/д/гггг ч:мм AM/PM.

Обратите внимание на пустое место в конце гггг и мм.

Новый формат будет доступен, когда он вам понадобится в списке Тип.

В ячейке B1 введите дату начала, включая месяц/день/год и время, используя "a" или "p" для обозначения AM и PM.

В ячейке B2 сделайте то же самое для даты окончания.

В ячейке B3 введите формулу =(B2-B1)*24.

Результат – 31,5 часа.

Примечание. В Excel для Интернета можно складывать и вычитать более 24 часов, но нельзя применять пользовательский числовой формат.

Добавить время

В ячейке B2 введите 6:45, а в ячейке B3 введите 9:30.

Введите =B2+B3 в ячейку B4 и нажмите клавишу ВВОД.

Выполнение двух задач займет 16 часов 15 минут.

Совет. Вы также можете суммировать время с помощью автосуммы. Щелкните в ячейке B4. Затем нажмите Главная > Автосумма. Формула будет выглядеть так: =СУММ(B2:B3). Нажмите Enter, чтобы получить результат, 16 часов и 15 минут.

Вычесть время

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

В ячейке B2 введите время начала, введите пробел, затем введите «a» для AM или «p» для PM и нажмите Enter. В ячейке C2 введите время окончания, включая «a» или «p» в зависимости от ситуации, и нажмите Enter. Введите другое время начала и окончания для ваших друзей Джой и Лесли.

В ячейке D2 вычтите время окончания из времени начала, введя формулу: =C2-B2, а затем нажмите клавишу ВВОД. Теперь мы видим, что Ричард работал 3 часа 45 минут.

Чтобы получить результаты для Джой и Лесли, скопируйте формулу, щелкнув ячейку D2 и перетащив ее в ячейку D4. Форматирование в ячейке D2 копируется вместе с формулой.

Вычисление разницы между двумя значениями времени в Excel может оказаться сложной задачей. Время обрабатывается внутри как числа от 0 до 1.

Простая формула

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

<р>1. Просто вычтите время начала из времени окончания.

Примечание. AM используется для обозначения времени ночи и утра. PM используется для времени днем и вечером. Измените формат времени, чтобы отображать время с AM/PM или без него.

<р>2. Измените числовой формат значений в ячейках A2, B2 и C2 на Общий.

Примечание. Время внутренне обрабатывается как число от 0 до 1. Например, 6:00 представлено как 0,25 (четверть дня).

Формула разницы во времени

Приведенная выше простая формула не работает, если время начала — до полуночи, а время окончания — после полуночи.

<р>2. Чтобы наглядно это увидеть, измените числовой формат значений в ячейках A2, B2 и C2 на Общий.

<р>3. Приведенная ниже формула разницы во времени работает всегда.

Объяснение: если время окончания больше или равно времени начала, просто вычтите время начала из времени окончания. Если нет, добавьте 1 (полный день) ко времени окончания, чтобы представить время на следующий день, и вычтите время начала.

Разница во времени в часах в виде десятичного числа

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

<р>1. Приведенная ниже формула делает свое дело.

Табель учета рабочего времени

Рассчитайте общее количество часов, обычные часы и сверхурочные часы с помощью нашего калькулятора табелей учета рабочего времени.

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

Добавить часы, минуты и секунды

Помните, чтобы ввести время, используйте ":" (двоеточие).

<р>1. Введите время в ячейку A1.

<р>2. Чтобы добавить к этому времени 2 часа 30 минут, введите приведенную ниже формулу.

Объяснение: функция ВРЕМЯ имеет 3 аргумента. Сначала используйте ЧАСЫ, МИНУТЫ и СЕКУНДЫ, чтобы создать время, введенное в ячейку A1. Затем добавьте 2 часа 30 минут.

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

<р>3. Выберите ячейку B1.

<р>4. Щелкните правой кнопкой мыши и выберите Формат ячеек (или нажмите CTRL + 1).

<р>5. В списке «Категория» выберите «Время» и выберите формат времени.

<р>7. Введите время в ячейку A1 (используйте 2 двоеточия, чтобы включить секунды).

<р>8. Чтобы добавить к этому времени 10 секунд, введите приведенную ниже формулу.

Примечание. Мы изменили формат времени на 13:30:55 (см. шаг 5).

Добавить время

Чтобы сложить время в Excel, просто используйте функцию СУММ. Чтобы отображалось более 24 часов, измените формат времени.

<р>1. Например, на выполнение задачи А требуется 8 часов, на выполнение задачи Б – 4 часа 30 минут и т. д.

Ширина

<р>2. Чтобы рассчитать общую продолжительность, просто используйте функцию СУММ.

<р>3. Измените продолжительность задачи D с 5 часов на 7 часов.

Чтобы отобразить в общей сложности более 24 часов, выполните следующие действия.

<р>4. Выберите ячейку B6.

<р>5. Щелкните правой кнопкой мыши и выберите Формат ячеек (или нажмите CTRL + 1).

<р>6. Выберите пользовательскую категорию.

<р>7. Введите следующий код числового формата: [ч]:мм

Примечание: посетите нашу страницу о форматах даты и времени, чтобы узнать больше об этой теме.

Вычесть минуты

Чтобы вычесть из времени часы, минуты или секунды, используйте функцию ВРЕМЯ (а также ЧАС, МИНУТ и СЕКУНД).

<р>1. Введите время в ячейку A1.

<р>2. Чтобы вычесть из этого времени 33 минуты, введите приведенную ниже формулу.

Разница во времени

Вычисление разницы между двумя значениями времени в Excel может оказаться сложной задачей.

<р>1. Чтобы вычислить разницу между двумя временами в один и тот же день, просто вычтите время начала из времени окончания.

Примечание. Мы изменили формат времени на 13:30 (см. шаг 5 в разделе "Добавить часы, минуты и секунды").

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

<р>3. Приведенная ниже формула вычисляет разницу между двумя значениями времени в часах в виде десятичного значения.

Примечание: посетите нашу страницу о разнице во времени, чтобы понять логику этих формул.

В предыдущей статье мы работали с функцией DATVALUE для преобразования дат из текста в порядковый номер. Теперь давайте поднимемся на ступеньку выше и рассмотрим некоторые формулы, которые позволят нам манипулировать датами и временем на листе.

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

Оглавление

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

Вычитание двух дат в Excel дает количество дней между этими датами. Сделать это, конечно, очень просто. Если вы легко отформатировали даты в любых двух ячейках, просто вычтите эти ячейки. Например:

Формула имеет простой процесс. Он берет обе даты (из ячеек B2 и A2) и вычитает их, чтобы вычислить количество дней между ними, то есть 584 дня в нашем примере. Однако убедитесь, что выходная ячейка не отформатирована как дата.

Похожие результаты можно получить и с помощью функции ДНЕЙ. Чтобы рассчитать количество дней между двумя датами, можно использовать функцию days как –

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

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

Но у этой формулы есть проблема: она будет возвращать отрицательные числа, если A4 имеет значение больше, чем сегодняшняя дата. Чтобы решить эту проблему, мы можем использовать функцию ЕСЛИ и создать формулу, которая дает более наглядный результат.

= ЕСЛИ ( СЕГОДНЯ () - A2 0, ABS ( СЕГОДНЯ () - A2) & "осталось дней..", "начало" & ABS ( СЕГОДНЯ () - A2) & "дней назад..")

Получить годы, месяцы и дни между двумя датами

Именно здесь все начинает немного усложняться.Мы познакомим вас с формулами — пристегните ремни безопасности.

Вот формулы, которые мы будем использовать:

= DATEDIF (B2, A2, "y") & "years" //Получает часть года
= DATEDIF (B2, A2, "ym") & "months" //Получает часть месяца (дни и годы игнорируются)
= DATEDIF (B2, A2, " md ") & " days " // Получает часть дня (месяцы и годы игнорируются)

Третий аргумент сообщает формуле, нужно ли вычесть дни, месяцы или годы ("y" для лет, "ym" для месяцев и "md" для дней).

Кроме того, если вы хотите вычесть все три компонента, вы можете добавить оператор конкатенации ("&") между этими формулами, чтобы вернуть все три компонента, как показано на рисунке, например так:

= РАЗНД ДАТА (B2,A2,"г")&"годы", " & РАЗНД ДАТА(B2,A2,"гм")&"месяцы," & РАЗНД ДАТА(B2,A2,"мд")&"дни"

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

Во-первых, формула вычисляет годы и добавляет строку "years", что дает в нашем примере результат "9 years". Вторая формула вычисляет месяцы и добавляет строку "months", чтобы получить результат "5 месяцев", а третья формула возвращает "21 день".

Когда вы добавляете оператор конкатенации между этими формулами, все возвращенные строки отображаются в одной ячейке, что дает нам окончательный результат "9 лет, 5 месяцев, 21 день".

Получить разницу между двумя датами (со временем) в часах

Прежде чем мы приступим к этим вычислениям, важно знать, как Excel интерпретирует время. Опять же, Excel любит работать с числами. Точно так же, как он присваивает порядковый номер дате, он присваивает дробь времени. Например, 0,25 — это 6 часов утра, а 0,5 — это полдень (или полдня).

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

При вычитании мы получаем дробь. Вот как Excel вычисляет этот доход: (Количество дней между обеими датами + доля разницы в количестве часов).

Давайте посчитаем эти числа, чтобы увидеть, что происходит внутри формулы.

Что происходит внутри скобок…

Общая разница между обоими значениями времени составляет 24 часа 30 минут 25 секунд. Excel сначала вычислит разницу в днях и вернет 1 день (т. е. 24 часа). При этом не учитываются 30 минут и 25 секунд, которые все еще необходимо учитывать при выводе. Поскольку наше время указано с точностью до секунд, мы вычислим 30 минут и 25 секунд как общее количество секунд, например:

30 минут * 60 секунд в минуту = 1800 секунд.
25 секунд

Поэтому общее количество секунд составляет 1825 секунд. Чтобы вычислить дробь, мы разделим 1825 секунд на 86 400 секунд (т. е. общее количество секунд в 1 дне).

Дробь, которую мы получим в результате, будет, естественно, выражаться в днях и может быть непосредственно добавлена к 1 дню, который мы вычислили ранее. Дробь, которую мы получаем при делении (1825/86400), равна 0,021122.

Отлично, так что окончательная разница между двумя датами DateTime составляет (1 + 0,02112 дня) или 1,02112 дня.

Выход из квадратных скобок

Вне скобок мы умножаем результат на 24.

Ну, нам нужен окончательный результат в часах, верно? Если наш текущий результат выражен в днях, умножив его на 24, мы преобразуем его в часы. Это даст нам (24 * 1,02112) 24,507 часа.

Теперь вы можете обойтись простой формулой, вложенной в функцию INT, и вы все равно получите почти такой же вывод без добавления текстовой строки в конце. Как я уже сказал, функция INT удаляет десятичные дроби, поэтому результат (24 часа) выглядит более приятным для глаз.

Получить разницу между двумя датами (со временем) в минутах

Естественно, здесь нет большой разницы. Единственная разница будет заключаться в том, что вместо 24 вы умножите в формуле 1440, а текстовую строку замените словом «минуты» вот так:

Формула работает так же, как описано в предыдущем примере.Единственная разница заключается в том, что вместо умножения скобки на 24 мы умножаем ее на 1440 (т. е. на общее количество минут в дне), поскольку мы хотим, чтобы наш вывод был выражен в минутах.

Получить разницу между двумя датами (со временем) в секундах

А, вы угадали!

Единственная разница, опять же, будет заключаться в том, что мы будем умножать 86400 внутри формулы и использовать текстовую строку «секунды» следующим образом:

Опять же, формула работает так же. На этот раз мы умножаем скобку на 86 400 (т. е. на общее количество секунд в сутках), поскольку нам нужен окончательный результат в секундах.

Получить дни, часы, минуты и секунды между двумя датами (со временем)

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

= INT (A2 - B2) & " days, "& TEXT (A2 - B2," h "" часы, "" m "" минуты, "" s "" секунды """) //где A2 EndDate и B2 — это StartDate

Хорошо, это может выглядеть как формула Франкенштейна, но на самом деле все совсем наоборот.

Здесь большую часть работы выполняет функция ТЕКСТ. Используя функцию ТЕКСТ, мы применяем пользовательский числовой формат (который мы обычно применяем с помощью диалогового окна «Формат ячеек») к выходным данным, сгенерированным путем вычитания обеих дат. Функция ТЕКСТ отвечает за часы, минуты и секунды в нашем окончательном выводе.

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

Наконец, мы связываем функции INT и TEXT вместе с оператором конкатенации, чтобы получить окончательный результат, то есть 1 день, 2 часа, 30 минут, 25 секунд.

Вычитание двух раз (без даты), чтобы найти разницу

Совершенно интуитивно это возможно только для вычислений, которые не превышают 24 часов. Для вычислений, превышающих 24 часа, см. раздел «Вычесть DateTime из другого DateTime».

Здесь не требуются отдельные формулы. Все, что нам нужно сделать, это вычесть две ячейки, например так:

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

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

Обратите внимание, как мы по-прежнему вычитаем A2 из B2 в этой формуле. ЕДИНСТВЕННОЕ отличие заключается в том, что вместо того, чтобы вручную применять пользовательский числовой формат, мы используем функцию ТЕКСТ, чтобы применить к выходным данным формат «ч:мм:сс».

Вычитание часов, минут или секунд из DateTime

Прежде чем мы углубимся в эти вычисления, обратите внимание, что функция ВРЕМЯ действительно может упростить нам вычитание времени, но у нее есть ограничение. Он позволяет вычитать максимум 23 часа или только 59 минут/секунд. Итак, мы воспользуемся некоторыми обходными путями и создадим формулу.

Вычитание часов из DateTime

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

Это простое вычисление, в котором мы хотим вернуться на 30 часов назад от полуночи 25 декабря 2001 года. Однако Excel вычисляет дату в днях. Итак, мы должны указать Excel количество дней, которые мы хотим вычесть из даты в ячейке A2. Для этого просто делим 30 на 24.

И как тогда Excel вычисляет время?

Обратите внимание, что при делении 30 на 24 получается 1,25 дня.Здесь числа после запятой обозначают время, а числа до запятой — дни.

Excel вычтет 1 день и (0,25 * 24 = 6) часов из даты в A2, потому что Excel считает время дробью/десятичным числом.

Наше возвращение в этом случае, конечно же, 23 декабря 2001 г., 18:00.

Хотя упомянутая выше формула работает во всех случаях, мы также можем использовать следующую формулу, если количество вычитаемых часов меньше или равно 23:

Вычитание минут из даты и времени

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

Как и в предыдущем примере, мы вводим количество минут, которое нужно вычесть, в числителе формулы, в нашем случае это 3500 минут. В знаменателе мы будем использовать общее количество минут в 1 дне (24 часа * 60 минут = 1440), чтобы преобразовать значение в скобках в дни из минут. Остальная часть логики остается такой же, как и в предыдущей формуле.

Это дает нам 22 декабря 2001 г., 14:10.

Вычесть секунды из DateTime

Именно — без изменений. Формула остается прежней, за исключением знаменателя в скобках:

Опять же, чтобы перевести секунды в дни, мы разделим числитель на количество секунд в сутках (24 часа * 60 минут * 60 секунд = 86 400).

Однако обратите внимание, что для отображения даты и времени в формате m/d/yyyy h:mm:ss вам нужно настроить формат ячейки следующим образом:

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

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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