Что произойдет, если вы добавите 1 единицу к дате в Excel

Обновлено: 06.07.2024

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

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

Как вычесть даты в Excel

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

Пример 1. Прямое вычитание одной даты из другой

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

Пример 2. Вычитание дат с помощью функции Excel DATEDIF

Если вышеприведенная формула выглядит слишком простой, вы можете добиться того же результата гуру-подобным способом, используя функцию РАЗНДАТ в Excel:

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

Пример 3. Вычитание даты из текущей даты

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

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

Пример 4. Вычитание дат с помощью функции Excel DATE

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

Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разницу в 5 дней:

=ДАТА(2015, 5, 20) - ДАТА(2015, 5, 15)

В заключение, когда дело доходит до вычитания дат в Excel и вы хотите узнать, сколько дней находится между двумя датами, имеет смысл выбрать самый простой и очевидный вариант — просто вычесть одну дату непосредственно из другой.< /p>

Если вам нужно подсчитать количество месяцев или лет между двумя датами, единственным возможным решением будет функция РАЗНДАТ. В следующей статье вы найдете несколько примеров формул, в которых эта функция будет подробно описана.< /p>

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

Как вычесть или прибавить дни к дате в Excel

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

Пример 1. Добавление дней к дате в Excel

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

Дату можно ввести несколькими способами:

В качестве ссылки на ячейку, например. =A2 + 10
Использование функции ДАТА(год, месяц, день), например =ДАТА(2015, 5, 6) + 10
В результате другой функции. Например, чтобы добавить заданное количество дней к текущей дате, используйте функцию СЕГОДНЯ(): =СЕГОДНЯ()+10

На следующем снимке экрана показаны приведенные выше формулы в действии. Текущая дата на момент написания: 6 мая 2015 г.:

Примечание. Результатом приведенных выше формул является порядковый номер, представляющий дату. Чтобы отобразить его в виде даты, выберите ячейку (ячейки) и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.На вкладке Число выберите Дата в списке Категория, а затем выберите нужный формат даты. Подробные инструкции см. в разделе Как изменить формат даты в Excel.

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

Чтобы вычесть заданное количество дней из определенной даты, вы снова выполняете обычную арифметическую операцию. Единственное отличие от предыдущего примера в том, что вместо плюса вы набираете минус :)

Вот несколько примеров формул:

=A2-10
=ДАТА(2015, 5, 6)-10
=СЕГОДНЯ()-10

Как прибавить или вычесть недели из даты

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

Добавление недель к дате в Excel:

Например, если вы добавляете 3 недели к дате в A2, используйте следующую формулу: =A2+3*7 .

Вычитание недель из даты в Excel:

Чтобы вычесть 2 недели из сегодняшней даты, введите =TODAY()-2*7 .

Как прибавить/вычесть месяцы к дате в Excel

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

Пример 1. Добавление месяцев к дате с помощью функции ДАТА в Excel

Возьмем, например, список дат в столбце A. Введите количество дат, которое вы хотите добавить (положительное число) или вычесть (отрицательное число) в какой-либо ячейке, например C2.

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

=ДАТА(ГОД(A2), МЕСЯЦ(A2) + $C$2, ДЕНЬ(A2))

Теперь посмотрим, что на самом деле делает функция. Логика формулы очевидна и проста. Функция ДАТА(год, месяц, день) принимает следующие аргументы:

год даты в ячейке A2;
месяц даты в A2 + количество месяцев, указанное в ячейке C2, и
день даты в A2.

Да, это так просто :) Если вы введете отрицательное число в C2, формула будет вычитать месяцы, а не добавлять их:

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

=ДАТА(ГОД(A2), МЕСЯЦ(A2) - $C$2, ДЕНЬ(A2))

И, конечно же, вы можете ввести число месяцев, которое нужно добавить или вычесть, в формулу вместо ссылки на ячейку:

Настоящие формулы могут выглядеть примерно так:

Добавить месяцы к дате: =ДАТА(ГОД(A2), МЕСЯЦ(A2) + 2, ДЕНЬ(A2))
Вычесть месяцы из даты: =ДАТА(ГОД(A2), МЕСЯЦ(A2) - 2, ДЕНЬ(A2))

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

Microsoft Excel предоставляет специальную функцию, которая возвращает дату, которая является заданным числом месяцев до или после даты начала — функция ДАТАМЕС. Он доступен в современных версиях Excel 2007, 2010, 2013 и готовящейся к выпуску Excel 2016.

В ваших формулах EDATE(дата_начала, месяцы) вы указываете следующие 2 аргумента:

Start_date — начальная дата, от которой нужно отсчитывать количество месяцев.
Месяцы — количество месяцев, которое нужно добавить (положительное значение) или вычесть (отрицательное значение).

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

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

Примечание. Функция Excel ДАТАМЕС возвращает порядковый номер, представляющий дату. Чтобы заставить Excel отображать его как дату, вы должны применить формат даты к ячейкам с вашими формулами EDATE. Подробные инструкции см. в разделе Изменение формата даты в Excel.

Как вычесть или прибавить годы к дате в Excel

Добавление лет к дате в Excel выполняется аналогично добавлению месяцев.Вы снова используете функцию ДАТА(год, месяц, день), но на этот раз указываете, сколько лет вы хотите добавить:

В листе Excel формулы могут выглядеть следующим образом:

=ДАТА(ГОД(A2) + 5, МЕСЯЦ(A2), ДЕНЬ(A2))

=ДАТА(ГОД(A2) - 5, МЕСЯЦ(A2), ДЕНЬ(A2))

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

Добавить/вычесть дни, месяцы и годы до даты

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

Чтобы добавить годы, месяцы, дни:

Чтобы вычесть годы, месяцы, дни:

Например, следующая формула добавляет 2 года 3 месяца и вычитает 15 дней из даты в ячейке A2:

=ДАТА(ГОД(A2) + 2, МЕСЯЦ(A2) + 3, ДЕНЬ(A2) - 15)

Применительно к нашему столбцу дат формула принимает следующий вид:

=ДАТА(ГОД(A2) + $C$2, МЕСЯЦ(A2) + $D$2, ДЕНЬ(A2) + $E$2)

Как складывать и вычитать время в Excel

В Microsoft Excel вы можете добавлять или вычитать время с помощью функции ВРЕМЯ. Она позволяет работать с единицами времени (часами, минутами и секундами) точно так же, как вы работаете с годами, месяцами и днями с помощью функции ДАТА.

Чтобы добавить время в Excel:

Чтобы вычесть время в Excel:

Где A2 содержит значение времени, которое вы хотите изменить.

Например, чтобы добавить 2 часа 30 минут и 15 секунд ко времени в ячейке A2, вы можете использовать следующую формулу:

=A2 + ВРЕМЯ(2, 30, 15)

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

=A2 + ВРЕМЯ(2, 30, -15)

Приведенная выше формула добавляет 2 часа 30 минут ко времени в ячейке A2 и вычитает 15 секунд.

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

=A2 + ВРЕМЯ($C$2, $D$2, $E$2)

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

Мастер формул даты и времени – быстрый способ добавления и вычитания дат в Excel

Теперь, когда вы знаете множество различных формул для вычисления дат в Excel, не хотели бы вы иметь только одну, которая может делать все это? Конечно, такой формулы никогда не может быть. Однако существует мастер даты и времени, который может построить любую формулу для вас на лету, при условии, что в вашем Excel установлен наш Ultimate Suite. Вот как:

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

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

Мастер создает формулу и показывает ее предварительный просмотр в ячейке. Он также показывает расчетную дату в разделе Результат формулы:

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

Это была довольно простая формула, не так ли? Давайте дадим мастеру что-то более сложное для работы. Например, давайте отнимем несколько лет, месяцев, недель и дней от даты в A2. Для этого перейдите на вкладку «Вычесть» и введите числа в соответствующие поля. Или вы можете ввести единицы измерения в отдельные ячейки и предоставить ссылки на эти ячейки, как показано на снимке экрана ниже:

При нажатии кнопки Вставить формулу в A2 вводится следующая формула:

Если вы планируете копировать формулу в другие ячейки, необходимо изменить все ссылки на ячейки, кроме A2, на абсолютные ссылки, чтобы формула копировалась правильно (по умолчанию мастер всегда использует относительные ссылки). Чтобы исправить ссылку, просто введите знак $ перед координатами строки и столбца, например:

И получите следующие результаты:

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

Если вы хотите поиграть с мастером формул даты и времени в своих рабочих листах, вы можете загрузить 14-дневную пробную версию Ultimate Suite.

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

Добавляет или находит разницу в значениях даты/времени и преобразует местное время в формат UTC.

Описание

Функция DateAdd добавляет количество единиц к значению даты/времени. Результатом является новое значение даты/времени. Вы также можете вычесть количество единиц из значения даты/времени, указав отрицательное значение.

Функция DateDiff возвращает разницу между двумя значениями даты/времени. Результатом является целое число единиц.

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

Функция TimeZoneOffset возвращает количество минут между местным временем пользователя и UTC (Всемирное координированное время).

Вы можете использовать DateAdd с TimeZoneOffset для преобразования между местным временем пользователя и UTC (Всемирное координированное время). Добавление TimeZoneOffset преобразует местное время в UTC, а его вычитание (добавление отрицательного значения) преобразует UTC в местное время.

Синтаксис

ДатаДобавить( ДатаВремя, Сложение [, Единицы ] )

Дата/время – обязательно. Значение даты/времени для работы.
Дополнение — обязательно. Число в единицах, добавляемое к DateTime.
Единицы — необязательно. Тип добавляемых единиц: миллисекунды, секунды, минуты, часы, дни, месяцы, кварталы или годы. Если не указано, используются дни.

DateDiff( StartDateTime, EndDateTime [, Единицы ] )

StartDateTime – обязательно. Начальное значение даты/времени.
EndDateTime – обязательно. Конечная дата/время.
Единицы — необязательно. Тип единиц для вычитания: миллисекунды, секунды, минуты, часы, дни, месяцы, кварталы или годы. Если не указано, используются дни.

TimeZoneOffset( [ DateTime ] )

Дата/время — необязательно. Значение даты/времени, для которого необходимо вернуть смещение. По умолчанию используется текущая дата/время.

Примеры

Во всех этих примерах предполагается, что текущая дата и время — 15 июля 2013 г., 13:02.

Простое добавление даты

Простая разница дат

Формула	Описание	Результат
DateDiff( Now (), DateValue("1/1/2014") )	Возвращает разницу между двумя единицами измерения в единицах по умолчанию в днях	170
DateDiff( Now(), DateValue("1/1/2014"), Months)	Возвращает разницу между двумя значениями в месяцах	6< /td>
DateDiff( Now(), Today(), Minutes )	Возвращает разницу между текущей датой/временем и только текущей датой (без времени) в минутах. Поскольку сейчас позже, чем сегодня, результат будет отрицательным.	-782

Разница дат с дробными результатами

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

Формула	Описание	Результат
DateDiff( TimeValue("09:45:00"), TimeValue("10:15:36"), Hours )	Минуты/секунды игнорируются, разница основана на времени до час.	1
DateDiff( TimeValue("09:45:00"), TimeValue("10:15:36"), минуты) /60	В разнице используются минуты, и результат делится на 60, чтобы получить разницу в часах.	0,5
DateDiff( TimeValue("09:45:00"), TimeValue("10:15:36"), Seconds )/3600	В разнице используются минуты и секунды; результат делится на 3600, чтобы получить разницу в часах.	0,51

Конвертация в формат UTC

Чтобы преобразовать в UTC (Всемирное координированное время), добавьте TimeZoneOffset для заданного времени.

Например, представьте, что текущая дата и время – 15 июля 2013 г., 13:02 по тихоокеанскому летнему времени (PDT, UTC-7). Чтобы определить текущее время в формате UTC, используйте:

ДатаДобавить(Сейчас(), TimeZoneOffset(), Минуты)

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

Чтобы увидеть результат, используйте функцию Text в формате дд-мм-гггг чч:мм, которая вернет 15-07-2013 20:02.

Конвертирование из UTC

Чтобы преобразовать из UTC, вычтите TimeZoneOffset (прибавив отрицательное значение) для заданного времени.

Например, представьте, что дата и время в формате UTC 15 июля 2013 г., 20:02 хранятся в переменной с именем StartTime. Чтобы настроить время для часового пояса пользователя, используйте:

DateAdd(StartTime, −TimeZoneOffset(StartTime), минуты)

Обратите внимание на знак "минус" перед TimeZoneOffset, чтобы вычесть смещение, а не добавить его.

Чтобы увидеть результат, используйте функцию "Текст" в формате дд-мм-гггг чч:мм, что даст 15-07-2013 13:02, если вы находитесь в тихоокеанском дневном свете. Время.

Сначала несколько новостей о предстоящем запуске Office 365, а затем совет о том, как суммировать диапазон дат в Excel.

Запуск Office 365

Вместо настольных версий Office Microsoft предлагает подписаться на онлайн-версию. Если вы хотите увидеть предварительную версию Office 365, вы можете посетить онлайн-запуск Microsoft продукта Office 365 завтра, в среду. 27 февраля. Есть две сессии:

8:00 по тихоокеанскому времени.
17:00 по тихоокеанскому времени.

Чтобы зарегистрироваться, нажмите здесь и заполните регистрационную форму: Сумма сумм в диапазоне дат

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

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

В этом примере на листе вводятся даты начала и даты окончания. Даты указаны в столбце А, а проданные единицы — в столбце Б.

Использование СУММЕСЛИМН для расчета итогового значения для диапазона дат

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

Вот формула, введенная в ячейку D5:

=СУММЕСЛИМН( $B$2:$B$9 , $A$2:$A$9 , «>=» & $D$2 , $A$2:$A$9 , « )

Диапазон $B$2:$B$9 содержит числа, которые мы хотим просуммировать.
Диапазон $A$2:$A$9 содержит даты продаж.
Первый критерий, «>=» & $D$2 – это диапазон со значением для критерия 1 (дата начала) и оператор, который следует использовать с этим значением (больше или равно)
Второй критерий – это диапазон со значением критерия 2 (дата окончания) и оператор, который следует использовать с этим значением (меньше или равно)

Проверить итог

В этом примере за выбранный диапазон дат продано 494 единицы. Чтобы проверить, вы можете выбрать ячейки B3: B6 и посмотреть на итоговую сумму, отображаемую в строке состояния Excel.

Чтобы получить общее количество единиц для другого диапазона дат, измените дату начала в ячейке D2 и/или дату окончания в ячейке E2.

Вычислить итог для диапазона дат с помощью СУММЕСЛИ

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

Чтобы просмотреть подробности и загрузить образец файла, посетите мой веб-сайт Contextures: суммирование сумм в диапазоне дат с помощью SUMIF

Посмотреть видео

Чтобы увидеть, как создать формулу СУММЕСЛИМН и проверить результат, посмотрите этот короткий видеоролик.

32 мысли о «Сумме для диапазона дат в Excel»

Спасибо, Дебра Далглиш, эта формула очень полезна для меня.

Как это можно сделать в Microsoft 365 Excel 2013? Возможно, вы упомянули, что есть более быстрый способ сделать это?

Здравствуйте, Бренда!
Я применил вашу формулу в Excel 2010 к чековому регистру, для которого я хотел, чтобы в ячейке отображались общие ежемесячные расходы. Однако формула отображает неправильную сумму. При необходимости могу загрузить файл excel для проверки. Я понимаю, что, возможно, я тоже что-то делаю не так.
Буду признателен за ответ.
Спасибо

Потратил часы, пытаясь найти лучший способ сделать это. Этот пост смог показать мне САМЫЙ ПРОСТОЙ способ сделать это. Большое спасибо! Большая помощь!

Это действительно хороший материал. Но какой была бы формула, если бы в один столбец были добавлены разные названия продуктов, такие как A, B, C, D и т. д. Вот еще один критерий для продукта: добавлено, например, 2 критерия даты и 1 критерий продукта, всего 3 критерия.
С нетерпением жду ответа.

Есть ли способ найти конкретную дату в диапазоне дат, чтобы найти сумму в другом столбце? Например, у меня есть ежемесячные даты в столбце F (8 = 38) и диапазоны дат, начинающиеся с A2 и заканчивающиеся на B10. Ежемесячные платежные суммы указаны в столбцах C2–C10. Я хочу найти сумму ежемесячного платежа за каждый месяц, указанный в столбце F:
Столбец A Столбец B Столбец C (премиальные суммы)
Строка 2 1/1/2012 31/12/2012 50,00 < br />Ряд 3 1/1/2013 15/6/2013 100,00
Ряд 4 16/6/2013 1/10/2015 65,00
и т. д.
Столбец F Столбец G (Prem Amt)
Ряд 8, 01.01.2012
Ряд 9, 01.08.2012
Ряд 10, 01.04.2013

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

Чтобы рассчитать количество месяцев между двумя датами как целое число, можно использовать функцию РАЗНДАТ. В показанном примере формула в D5, скопированная вниз, выглядит следующим образом:

Примечание. DATEDIF автоматически округляется до ближайшего целого месяца. Чтобы округлить до ближайшего месяца, см. ниже.

В этом примере цель состоит в том, чтобы вычислить целые месяцы между двумя действительными датами Excel. Это немного сложная задача в Excel, потому что количество дней в каждом месяце варьируется, поэтому правила расчета целого месяца не очевидны. Решение, описанное ниже, использует функцию DATEDIF. DATEDIF — это функция «совместимости», изначально появившаяся в Lotus 1-2-3. Вы можете использовать DATEDIF во всех текущих версиях Excel, но вы должны ввести функцию вручную — DATEDIF не будет отображаться в качестве предлагаемой функции в строке формул, и Excel не поможет вам с аргументами функции. См. эту страницу функции DATEDIF для получения дополнительной информации о доступных аргументах.

Полные месяцы

В показанном примере мы вычисляем полные месяцы между датой начала и датой окончания с помощью функции DATEDIF. DATEDIF принимает 3 аргумента: start_date, end_date и единица измерения. В данном случае нам нужны месяцы, поэтому мы указываем «m» для единицы. В показанном примере формула в D5, скопированная вниз, выглядит следующим образом:

Поведение DATEDIF является автоматическим. Результатом является целое число, представляющее полные месяцы между датой начала в столбце B и датой окончания в столбце C. Обратите внимание, что результат в строках 5, 6 и 7 точно кратен месяцам, но результат в D8 имеет округлено в меньшую сторону.

Также обратите внимание, что результаты в строках с 12 по 15 немного странные из-за того, как DATEDIF обрабатывает даты конца месяца. В основном, когда дни в начальном и конечном месяцах совпадают, результат является точным кратным месяцам. Однако если дни не совпадают, результаты могут быть неожиданными. Например, с начальной датой 31 июля (конец месяца) и конечной датой 30 августа (конец месяца) результат равен нулю, хотя большинство людей посчитает это за 1 месяц. Одно из решений этой проблемы описано ниже.

Ближайший целый месяц

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

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

Десятичные месяцы

DATEDIF работает только с целыми месяцами. Чтобы вычислить десятичное число, представляющее количество месяцев между двумя датами, вы можете использовать функцию YEARFRAC следующим образом:

YEARFRAC возвращает дробные части года между двумя датами, поэтому результатом является приблизительное количество месяцев между двумя датами, включая дробные месяцы, возвращаемые в виде десятичного значения. На приведенном ниже экране показано, как исходная формула DATEDIF сравнивает версию YEARFRAC:

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

Альтернативная формула

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

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

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

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

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

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

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

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