Как вычесть дату из даты в Excel и получить количество лет

Обновлено: 21.11.2024

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

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

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

Как вычитать даты в Excel

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

Перед началом обязательно отформатируйте ячейки (⌘+1 в macOS и CTRL+1 в Windows) в соответствии с категорией даты и числа, необходимой для каждого типа вычитания.

1. Вычитание месяцев в Excel

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

  1. Введите даты начала месяца в столбце A.
  2. Чтобы вычесть месяцы, введите отрицательное значение в столбце B, добавив (-) перед числовым значением.
  3. Вставьте =ДАТАМЕС(A2,B2) в ячейку C2, скопируйте и настройте значения A и B формулы в соответствии с их строками, пока не будут учтены все даты.

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

2. Вычитание дней из даты в Excel

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

  1. Введите исходные даты в столбце A.
  2. Чтобы вычесть дни, введите отрицательное значение в столбце B, добавив (-) перед числовым значением.
  3. В ячейке C2 введите =A2+B2 и продолжайте изменять значения по мере продвижения вниз.

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

3. Вычитание лет из даты в Excel

Если вы хотите вычесть годы из дат, вы можете использовать формулу =ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2),ДЕНЬ(A2)). При использовании этой формулы для функции ДАТА требуются значения функции МЕСЯЦ и ДЕНЬ; исходные значения остаются неизменными.

Вместо этого год будет меняться в зависимости от значений, помещенных в столбец B; функция ДАТА будет использовать все данные для вычитания лет из начальной даты.

<р>1. Введите начальные даты в столбце A.

<р>2. Чтобы вычесть годы, введите отрицательное значение в столбце B, добавив (-) перед числовым значением.

<р>3. В ячейке C2 введите =ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2),ДЕНЬ(A2)) и продолжайте изменять значения по мере работы вниз.

4. Вычитание дней, месяцев и лет из даты в Excel

Хотя вы можете не усложнять и изменять только одно значение за раз; сила Excel заключается в возможности одновременного внесения нескольких изменений. В этом случае формула =ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2)+C2,ДЕНЬ(A2)+D2) допускает три вычитания одновременно в случае лет, месяцев и дней.

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

  1. Введите даты начала в столбце A.
  2. В столбцах B, C и D введите изменения отрицательных значений, чтобы применить соответствующие годы, месяцы и дни.
  3. В столбце E введите формулу =ДАТА(ГОД(A2)+B2,МЕСЯЦ(A2)+C2,ДЕНЬ(A2)+D2), корректируя значение по мере работы со списком дат.

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

Использование Excel для обнаружения дней между датами

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

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

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

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

Скопируйте образец данных

Скопируйте следующие образцы данных в ячейку A1 на чистом листе.

Создать план проекта

На листе выберите ячейку A1 и нажмите + V .

Рассчитать разницу в днях

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

Выберите ячейку D2, которая является первой пустой ячейкой в ​​столбце "Продолжительность".

Введите =C2-B2 и нажмите RETURN .

Excel отображает результат как количество дней между двумя датами (104).

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

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

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

Выберите ячейку D2, которая является первой пустой ячейкой в ​​столбце "Продолжительность".

Если ячейка не пуста, в меню "Правка" выберите "Очистить", а затем нажмите "Все".

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

В Excel для Mac 2011 на вкладке "Формулы" в разделе "Функция" нажмите "Построитель формул".

В Excel 2016 для Mac на вкладке "Формулы" нажмите "Вставить функцию".

В поле поиска построителя формул введите ЧИСТРАБДНИ.

В появившемся списке дважды щелкните ЧИСТРАБДНИ.

В разделе "Аргументы" установите флажок рядом с start_date, а затем на листе выберите ячейку B2 (8/6/2011)

В разделе "Аргументы" установите флажок рядом с end_date, а затем на листе выберите ячейку C2 (20 сентября 2011 г.)

Оставьте поле рядом с праздниками пустым.

Результат – 75 рабочих дней без учета праздников.

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

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

Совет. См. раздел ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД, чтобы узнать больше об этих формулах, в том числе о том, как учитывать праздничные дни.

Рассчитать количество месяцев между двумя датами в одном и том же году

Excel обрабатывает месяцы как значения (от 1 до 12), что упрощает вычитание более ранней даты из другой даты, если обе даты приходятся на один и тот же год. Используйте функцию МЕСЯЦ, чтобы получить значение месяца для каждой даты, а затем рассчитайте разницу между значениями.

Выберите ячейку D2, которая является первой пустой ячейкой в ​​столбце "Продолжительность".

Если ячейка не пуста, в меню "Правка" выберите "Очистить", а затем нажмите "Все".

В ячейке D2 введите =МЕСЯЦ(C2)-МЕСЯЦ(B2) и нажмите клавишу RETURN .

Результат равен 3.

Рассчитать количество месяцев между двумя датами в разные годы

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

Если ячейка не пуста, в меню "Правка" выберите "Очистить", а затем нажмите "Все".

В ячейке D3 введите =(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3), а затем нажмите RETURN .

Результат равен 9.

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

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

Если ячейка не пуста, в меню "Правка" выберите "Очистить", а затем нажмите "Все".

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

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

В этом примере дата начала указана в ячейке D9, а дата окончания — в ячейке E9. Формула находится в F9. «d» возвращает количество полных дней между двумя датами.

Разница в неделях

В этом примере дата начала указана в ячейке D13, а дата окончания — в ячейке E13. «d» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите CTRL + 1. Затем нажмите Число > Десятичные разряды: 2.

Разница в месяцах

В этом примере дата начала указана в ячейке D5, а дата окончания — в ячейке E5. В формуле «m» возвращает количество полных месяцев между двумя днями.

Разница в годах

В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. «y» возвращает количество полных лет между двумя днями.

Рассчитать возраст в накопленных годах, месяцах и днях

Вы также можете рассчитать возраст или стаж работы. Результатом может быть что-то вроде «2 года, 4 месяца, 5 дней».

1. Используйте DATEDIF, чтобы найти общее количество лет.

В этом примере дата начала указана в ячейке D17, а дата окончания — в ячейке E17. В формуле «y» возвращает количество полных лет между двумя днями.

2. Используйте DATEDIF снова с «ym», чтобы найти месяцы.

В другой ячейке используйте формулу DATEDIF с параметром "ym". «Гм» возвращает количество оставшихся месяцев после последнего полного года.

3. Используйте другую формулу, чтобы найти дни.

Теперь нам нужно найти количество оставшихся дней. Мы сделаем это, написав другую формулу, показанную выше. Эта формула вычитает первый день последнего месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: Сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. Тогда 1 представляет первый день этого месяца. Результат функции ДАТА — 01.05.2016. Затем мы вычитаем это из исходной даты окончания в ячейке E17, то есть 06.05.2016. 06.05.2016 минус 01.05.2016 равно 5 дням.

Предупреждение. Мы не рекомендуем использовать аргумент DATEDIF "md", так как это может привести к неточным результатам.

4. Необязательно: объедините три формулы в одну.

Вы можете поместить все три вычисления в одну ячейку, как в этом примере. Используйте амперсанд, кавычки и текст. Это более длинная формула для ввода, но, по крайней мере, все в одном. Совет: нажмите клавиши ALT+ВВОД, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, нажмите CTRL+SHIFT+U, если вы не видите всю формулу.

Скачать наши примеры

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

Другие расчеты даты и времени

Как вы видели выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо того, чтобы вводить конкретные даты, вы также можете использовать функцию СЕГОДНЯ() внутри формулы. Когда вы используете функцию СЕГОДНЯ(), Excel использует в качестве даты текущую дату вашего компьютера. Имейте в виду, что это изменится, когда файл снова откроется в будущем.

Обратите внимание, что на момент написания этой статьи это было 6 октября 2016 года.

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

Прежде чем начать: решите, хотите ли вы исключить праздничные дни. Если вы это сделаете, введите список дат праздников в отдельной области или листе. Поместите каждую дату праздника в отдельную ячейку. Затем выберите эти ячейки, выберите «Формулы» > «Определить имя». Назовите диапазон MyHolidays и нажмите OK. Затем создайте формулу, выполнив указанные ниже действия.

1. Введите дату начала и дату окончания.

В этом примере дата начала указана в ячейке D53, а дата окончания — в ячейке E53.

2. В другой ячейке введите следующую формулу:

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

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример в Excel 2007 выглядел бы так: =ЧИСТРАБДНИ(D53,E53). Вы не указываете 1, потому что ЧИСТРАБДНИ предполагают, что выходные приходятся на субботу и воскресенье.

3. При необходимости измените 1.

Если суббота и воскресенье не являются вашими выходными днями, измените 1 на другое число из списка IntelliSense. Например, число 2 делает воскресенье и понедельник выходными днями.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ Excel 2007 всегда предполагает, что выходные приходятся на субботу и воскресенье.

4. Введите название диапазона праздников.

Если вы создали название праздничного диапазона в разделе "Перед началом работы" выше, введите его в конце следующим образом. Если у вас нет праздников, вы можете не указывать запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: =ЧИСТРАБДНИ(D53,E53,Моипраздники).

Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например D35:E:39. Или вы можете ввести каждый праздник внутри формулы. Например, если ваши праздники приходятся на 1 и 2 января 2016 года, вы должны ввести их так: =NETWORKDAYS.INTL(D53,E53,1,). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53,E53,)

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

1. Введите время начала и время окончания.

В этом примере время начала указано в ячейке D80, а время окончания — в ячейке E80. Обязательно введите часы, минуты и с пробелом перед AM или PM.

2. Установите формат ч:мм AM/PM.

Выберите обе даты и нажмите CTRL + 1 (или + 1 на Mac). Обязательно выберите «Пользовательский» > «ч:мм AM/PM», если он еще не установлен.

3. Вычтите два раза.

В другой ячейке вычтите ячейку времени начала из ячейки времени окончания.

4. Установите формат ч:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите «Пользовательский» > «ч:мм», чтобы результат не включал AM и PM.

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

1. Введите две полные даты и время.

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

2. Установите формат 14.03.12 13:30.

Выделите обе ячейки и нажмите CTRL + 1 (или + 1 на Mac). Затем выберите Дата > 14.03.12 13:30. Это не дата, которую вы установите, это просто пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другую примерную дату, например 14 марта 2001 г., 13:30.

3. Вычтите два.

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

4. Установите формат [ч]:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите Пользовательский. В поле Тип введите [ч]:мм.

В предыдущей статье мы работали с функцией 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" (формат для печати), которая резко повысит вашу продуктивность.

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