С помощью функции РАЗНДАТ можно вычислить разность между двумя датами. Поместите начальную дату в одну ячейку, а конечную - в другую. Затем введите формулу (см. одну из формул ниже).
Разность в днях
В этом примере начальная дата находится в ячейке D9, а конечная - в ячейке E9. Формула помещена в ячейку F9. Параметр "d" возвращает количество полных дней между двумя датами.
Если вы следовали за нашей серией блога, вы уже знаете, как вставлять и форматировать даты в своих листах, как рассчитать, а также. Все три аргумента необходимы. Хотя этот метод полностью поддерживается, этот метод не является надежным, поскольку нумерация дат зависит от разных компьютерных систем. Например, следующая формула подсчитывает, сколько дней между сегодняшней датой и 20 мая. Единица - единица времени, используемая при расчете разницы между двумя датами. В целом доступно 6 единиц, которые описаны в следующей таблице.
Надеемся, что приведенная выше информация была полезной для понимания основ. Какой из них зависит от ваших потребностей. Если вы ищете формулу, которая может вернуть разницу дат в дни как положительное или отрицательное число, просто вычтите одну дату непосредственно из другой.
Разность в неделях
В этом примере начальная дата находится в ячейке D13, а конечная - в ячейке E13. Параметр "d" возвращает количество дней. Но в конце формулы указано выражение /7 . Это выражение делит количество дней на 7, поскольку в неделе 7 дней. Учтите, что этот результат также должен иметь числовой формат. Нажмите клавиши CTRL+1. Затем выберите Числовой > Число десятичных знаков: 2 .
Подсчет месяцев между двумя датами, игнорирующими годы
Однако результаты, возвращаемые двумя формулами, различаются: это несколько строк.
Расчет полных лет между двумя датами
Это связано с тем, что количество полных календарных лет между датой начала и окончания равно нулю. Расчет лет между двумя датами. Аналогично тому, как вы извлекаете год из каждой даты, а затем вычитаете годы друг от друга.Как получить разницу дат в днях, месяцах и годах
В большинстве случаев результаты идентичны, за исключением того, что. Формула отображает только ненулевые элементы, как показано на следующем скриншоте. Вышеприведенная формула вычисляет количество полных лет. И вы получите следующий результат. Чтобы узнать другие методы преобразования даты рождения в возраст, проверьте.
Разность в месяцах
В этом примере начальная дата находится в ячейке D5, а конечная - в ячейке E5. Параметр "m" в формуле возвращает количество полных месяцев между двумя датами.
Разность в годах
В этом примере начальная дата находится в ячейке D2, а конечная - в ячейке E2. Параметр "y" возвращает количество полных лет между двумя датами.
Результат будет выглядеть примерно так. В качестве другого примера давайте разницу между двумя датами в годах, месяцах, неделях и днях. В зависимости от компоновки и дизайна вашего рабочего листа вы можете представить результаты одним из следующих способов.
Для этого вы выбираете поле Добавить текстовую метку и переключатель коротких меток. Чтобы удалить текстовые метки из результатов, снимите флажок Добавить текстовую метку. В этом случае единичный блок будет выводиться как число, единицы умножения будут разделены прямыми косыми чертами, как показано на скриншоте ниже. Мастер поддерживает все возможные единицы и позволяет выбирать из 11 различных комбинаций этих устройств.
- Добавьте полные текстовые метки, такие как дни, месяцы, недели, годы.
- Это пример по умолчанию, продемонстрированный в приведенных выше примерах.
- Показывать текстовые метки.
- Разница всегда вычисляется идеально, даже если дата 1 больше, чем Дата 2.
Вычисление возраста в годах, месяцах и днях
Можно также вычислить возраст или срок службы. Результат при этом будет иметь следующий вид: "2 года, 4 месяца, 5 дней".
1. Используйте функцию РАЗНДАТ для вычисления суммарного количества лет.
В этом примере начальная дата находится в ячейке D17, а конечная - в ячейке E17. Параметр "y" в формуле возвращает количество полных лет между двумя датами.
Мы также можем ссылаться на ячейку, содержащую дату. Мы можем изменить это, добавив любое из следующих чисел ко второму аргументу. Важно отметить, что функция вычисляет только количество недель с указанной даты в том же году. Неделя начинается в воскресенье, поэтому из-за того же дня может появиться другой номер недели.
Было бы здорово, если бы был третий аргумент, который позволил нам определить день года для начала. Это было бы полезно для календарей финансового года. К сожалению, у нас пока нет этой функции. Ознакомьтесь с моей информацией о том, как даты хранятся в виде чисел.
2. Используйте функцию РАЗНДАТ с параметром "ym" для вычисления количества месяцев.
В еще одну ячейку поместите формулу РАЗНДАТ с параметром "ym" . Параметр "ym" возвращает количество месяцев, остающихся после последнего полного года.
3. Используйте другую формулу, чтобы найти дни.
Теперь нужно определить количество оставшихся дней. Мы сделаем это, написав другую формулу, чем приведенная выше. Эта формула вычитает первый день конечного месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Ниже описано, как это делается. Сначала, функция ДАТА создает дату, 01.05.2016. Она делает это на основе года и месяца в ячейке E17 (число 1 означает первый день месяца). Функция ДАТА отображает 01.05.2016. Затем мы вычитаем эту дату из исходной конечной даты в ячейке E17, т. е. 06.05.2016. "06.05.2016" минус "01.05.2016" равняется 5 дням.
# 2 - Рассчитайте количество недель между датами
Чтобы определить, сколько недель прошло между двумя датами, мы можем использовать простую формулу, чтобы найти количество дней между датами, а затем разделить на. Формула вернет десятичное число. Мы можем изменить формат номера на Фракцию, чтобы отобразить количество дней.
# 3 - отобразить слово «неделя» в формате номера
Эта формула также может использоваться для обратного отсчета количества недель до следующего дня рождения, отпуска, праздника, квартала, конца года и т.д. дата будет обновляться с текущей датой каждый раз, когда мы открываем или вычисляем книгу. Мы могли бы отобразить слово «Неделя» до номера недели в ячейке. Мой предпочтительный метод заключается в использовании пользовательского формата номера для добавления слова «Неделя» до номера недели. Преимущество формата пользовательского номера заключается в том, что число остается числом вместо текста.
4. Необязательно: объедините три формулы в одну.
Все три вычисления можно поместить в одну ячейку, как в этом примере. Можно использовать амперсанды, кавычки и текст. Такую формулу дольше вводить, но зато здесь все вычисления в одном месте. Совет. Чтобы добавить в формулу разрыв строки, нажмите клавиши ALT+ВВОД. Это упростит чтение. Если не видно всю формулу целиком, нажмите клавиши CTRL+SHIFT+U.
Это упрощает сортировку по количеству недель в отфильтрованных диапазонах и сводных таблицах. Если номер сохранен как текст, он не будет сортироваться должным образом, если мы не используем две цифры для номера недели. В этом уроке вы узнаете, как рассчитать количество.
Вы должны указать «Дата начала» и «Дата окончания» в функции «Дни», и это даст вам общее количество дней между двумя указанными датами. Следующая формула даст вам общее количество дней между двумя датами. Обратите внимание, что вы также можете вручную указать даты в функции «Дни», поставив их в двойные кавычки.
Скачивание образцов
Вы можете скачать образец книги со всеми примерами из этой статьи и использовать как готовые, так и свои собственные формулы.
Другие вычисления даты и времени
Разность между сегодняшней и другой датой
Как было показано выше, функция РАЗНДАТ вычисляет разность между начальной и конечной датами. Но вместо ввода конкретной даты можно использовать в формуле функцию СЕГОДНЯ() . При этом Excel в качестве даты берет текущую дату компьютера. Помните, что она изменится, когда файл будет снова открыт в другой день.
Вы должны использовать это, когда выходные дни - суббота и воскресенье. - используйте это, когда выходные дни не в субботу и воскресенье. Давайте сначала рассмотрим пример, где вы хотите рассчитать количество рабочих дней между двумя датами в субботу и воскресенье в выходные дни.
Эта функция отлично работает в большинстве случаев, за исключением тех, где выходные - дни, кроме субботы и воскресенья. Например, в странах Ближнего Востока выходные - пятница и суббота, или на некоторых рабочих местах у людей может быть шестидневная рабочая неделя.
Вычисление количества рабочих дней с учетом или без учета праздников
Вычислить количество рабочих дней между двумя датами можно с помощью функции ЧИСТРАБДНИ.МЕЖД. При этом можно также исключить выходные и праздничные дни.
Перед началом. Решите, нужно ли исключить праздничные даты. Если да, введите список праздничных дней в отдельной области или на отдельном листе. Поместите каждую праздничную дату в отдельную ячейку. Затем выделите эти ячейки, выберите Формулы > Присвоить имя . Присвойте диапазону имя МоиПраздники и нажмите кнопку ОК . Затем создайте формулу, выполнив указанные ниже действия.
Теперь давайте посмотрим пример расчета количества рабочих дней между двумя датами, где выходные дни - пятница и суббота. Предположим, у вас есть набор данных, как показано ниже. Чтобы рассчитать количество рабочих дней с выходными в пятницу и субботу, используйте следующую формулу.
Рассчитать количество рабочих дней в неполной задаче
Третий аргумент в этой формуле говорит, что формула рассматривает пятницу и субботу как выходные. Предположим, у нас есть набор данных, как показано ниже. Вот формула, которая даст вам общее количество выходных дней между двумя датами. Давайте возьмем пример, где вы участвуете в проекте, где вы должны работать неполный рабочий день.
1. Введите начальную и конечную даты.
В этом примере начальная дата находится в ячейке D53, а конечная - в ячейке E53.
2. Еще в одной ячейке введите формулу такого вида:
Введите формулу, как в приведенном выше примере. Параметр "1" в формуле назначает субботы и воскресенья выходными днями и исключает их из общей суммы.
Вычислить дату после нескольких дней
Вот формула, которая сделает это. Также подсказку: Рассчитать со временем. Если вы переключитесь на вид формулы с помощью клавиши, вы можете отобразить базовый номер. Чтобы эта функция была доступна, вы должны включить надстройку анализа. Ознакомьтесь с разделом «Известные проблемы» этой статьи для получения более подробной информации.
Единица Тип информации, которую вы хотите вернуть. Если вы пытаетесь вычислить дни, которые отсутствуют после последнего завершенного месяца, существует обходное решение. Затем 1 представляет собой первый день месяца. Однако для получения результатов всегда необходимо ссылаться на параметры на английском языке. Интервал - это единица времени, с помощью которой можно вычислить разницу.
Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Но есть функция ЧИСТРАБДНИ. Функция из примера выше в Excel 2007 имела бы такой вид: =ЧИСТРАБДНИ(D53;E53) . Не нужно указывать 1, потому что функция ЧИСТРАБДНИ по умолчанию считает субботу и воскресенье выходными днями.
3. При необходимости измените параметр "1".
Если у вас выходные дни не в субботу и воскресенье, укажите вместо "1" другое число из списка IntelliSense. Например, "2" назначает выходными днями воскресенья и понедельники.
Разность в месяцах
Интервал должен быть одним из следующих. Любая более ранняя дата, не являющаяся допустимым значением, генерирует ошибку в формулах между датами. Как показано в примере, дата должна быть введена в виде строки. Если мы хотим извлечь значение дня, месяца или года даты, мы будем использовать соответствующие формулы. Итак, с датой предыдущего примера мы будем иметь.
Используя эти три функции, мы перешли от даты к ее компонентам. Если мы хотим рассчитать разницу между двумя датами, достаточно будет использовать оператор для вычитания соответствующих серийных номеров или ссылок на ячейки, в которых сохраняются даты.
Если у вас Excel 2007, пропустите это действие. Функция ЧИСТРАБДНИ в Excel 2007 всегда считает выходными днями субботу и воскресенье.
4. Введите имя диапазона праздничных дней.
Если вы присвоили имя диапазону праздничных дней в соответствии с инструкциями раздела "Перед началом", введите в конце это имя, как здесь. Если у вас нет праздничных дней, можно оставить точку с запятой и не указывать МоиПраздники. В Excel 2007 формула из примера выше примет вид: =ЧИСТРАБДНИ(D53;E53;МоиПраздники) .
Предположим, у нас есть таблица с именами и их соответствующими датами рождения. Если мы хотим получить тот же результат, вставив даты в формулу, мы должны сначала преобразовать их в их серийный номер. Добавить дни, месяцы, годы до даты. Чтобы добавить дни к дате, поскольку один день соответствует единице серийного номера, соответствующей дате, просто добавьте количество дней, которое требуется для серийного значения даты. Это немного сложно, если мы должны добавить месяцы или годы.
Округление в большую сторону
Чтобы добавить месяцы или годы к дате, мы можем разбить ее на 3 части с соответствующими формулами, добавить интересующую нас часть и затем собрать ее в результирующую дату. Другая полезная информация при работе с датами - это расчет номера недели. Основными альтернативами являются 1, чтобы иметь «воскресенье» в качестве первого дня недели и 2 для «понедельника».
Совет. Если вы не хотите ссылаться на имя диапазона праздничных дней, можете вместо него указать диапазон D35:E:39 . Можно также ввести в формулу каждый праздничный день. Например, если праздничные дни приходятся на 1 и 2 января 2016 г., введите их следующим образом: =ЧИСТРАБДНИ.МЕЖД(D53;E53;1;{"01.01.2016";"02.01.2016"}) . В Excel 2007 нужно ввести: =ЧИСТРАБДНИ(D53;E53;{"01.01.2016";"02.01.2016"})
В разделе Известные проблемы этой статьи. Даты сохраняются последовательно в виде серийных номеров. . Если вы попытаетесь рассчитать оставшиеся дни после последнего завершенного месяца, вы можете использовать это альтернативное решение. Предположим, мы хотим изменить дату планирования проекта, добавив две недели, чтобы узнать, какова будет новая дата завершения, или определить время, необходимое для завершения деятельности в списке деятельности проекта.
Добавить или вычесть дни с даты
Предположим, что баланс вашего аккаунта ожидается 8 февраля. Вы хотите перевести средства на свой текущий счет, чтобы вам зачисляли 15 календарных дней до этой даты.
Добавить или вычесть месяцы с даты
Эта формула возвращает дату 09. Например, предположим, что вы хотите добавить 16 месяцев до 16 октября.Вычисление затраченного времени
Чтобы вычислить затраченное время, нужно из одного значения времени вычесть другое. Поместите начальное время в одну ячейку, а конечное - в другую. Обязательно указывайте полное значение времени, включая часы и минуты. Ниже это описано более подробно.
1. Введите начальное и конечное время.
В этом примере начальное время находится в ячейке D80, а конечное - в ячейке E80. Обязательно укажите полное значение времени, включая часы и минуты.
Добавить или вычесть годы с даты
В этом случае функция использует значение даты, которое вводится напрямую, то есть «09». На вкладке «Главная» в группе «Ячейки» нажмите «Формат», а затем «Формат ячейки». В диалоговом окне «Формат ячеек» перейдите на вкладку «Число». Значение каждой ячейки теперь должно отображаться как дата вместо серийного номера. Вы можете использовать аналогичную формулу для добавления месяцев к дате. Эта формула возвращает дату 10.
Добавьте комбинацию дней, месяцев и лет к дате
Пример может быть проще понять, если вы скопируете его на пустой лист. Формулы в примере используют следующие аргументы: Аргумент - это значение, предоставляемое для функции. В каждой из формул к дате, содержащейся в ячейке А, добавляется определенное количество лет, месяцев и дней.
2. Задайте формат чч:мм.
Выберите обе даты и нажмите клавиши CTRL+1 (или +1 на компьютере с Mac OS). Выберите формат (все форматы) > чч:мм , если он еще не задан.
3. Вычислите разность двух значений времени.
В еще одной ячейке вычтите значение ячейки начального времени из значения ячейки конечного времени.
4. Задайте формат чч:мм.
Нажмите клавиши CTRL+1 (или +1 на компьютере с Mac OS). Выберите (все форматы) > чч:мм .
Вычисление затраченного времени между двумя датами и значениями времени
Чтобы вычислить время между двумя датами и значениями времени, можно просто вычесть одного из другого. Но к каждой ячейке необходимо применить форматирование, чтобы приложение Excel вернуло нужный результат.
Функция DATEDIF (РАЗНДАТ) в Excel используется для подсчета количества лет, месяцев или дней между двумя датами. Хороший пример – подсчет возраста.
Число, обозначающее количество лет/месяцев/дней между двумя датами. Какие значения будут отображаться (год, месяц или дни) зависит от того, что будет указано вами в атрибутах функции.
Синтаксис
=DATEDIF(start_date,end_date,unit) – английская версия
=РАЗНДАТ (нач_дата ; кон_дата ; единица ) – русская версия
Аргументы функции
- start_date (нач_дата) : дата, с которой начинается вычисление разницы во времени между двумя датами. Дата может быть указана как текст в кавычках, числовое значение, или результат какого-либо вычисления (например в функции ДАТА());
- end_date (кон_дата) : дата, на которой вычисление разницы во времени между двумя датами будет окончено. Она, также, может быть указана как текст в кавычках, числовое значение, или результат какого-либо вычисления;
- unit (единица) : Этот аргумент определяет в каком измерении будет отображена разница между двумя датами (год, месяц, день). Существует 6 различных типов отражения данных:
- “Y” – возвращает данные в количестве лет между двумя датами;
- “M” – возвращает данные в количестве месяцев между двумя датами;
- “D” – возвращает данные в количестве дней между двумя данными;
- “MD” – возвращает данные в количестве дней между двумя датами, но не учитывает целые годы, месяца, которые уже прошли при расчете данных. Например, если вы рассчитываете количество дней между двумя датами: 1 Января 2017 и 3 Марта 2017, функция выдаст значение “2”, так как не будет учитывать два прошедших полных месяцев (Январь, Февраль);
- “YM” – возвращает данные в количестве месяцев между двумя датами, но не учитывает целые годы, которые уже прошли между двумя расчетными данными;
- “YD” – возвращает данные в количестве дней между двумя датами, но не учитывает целые годы, которые уже прошли между двумя расчетными датами.
Дополнительная информация
- При вводе функции в ячейку, она не отображается в быстром меню выбора функций. Тем не менее, функция работает во всех версиях Excel. Важно знать аргументы функции и как ими пользоваться;
- Данные даты в функции могут быть введены как:
– дата указанная в виде текста в кавычках;
– число;
– как результат вычисления, формулы, функции.
Примеры использования функции РАЗНДАТ в Excel
Пример №1. Подсчет количества лет между двумя датами
На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.
Пример №2. Подсчет количества месяцев между двумя датами
На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество полных месяцев между двумя датами – 19 июня 1989 года и 26 марта 2017 года.
Функция возвращает значение “333”, что является количеством полных месяцев между датами указанными выше.
Пример №3. Подсчет количества дней между двумя датами
На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество дней между двумя датами. Например, функция возвращает значение “10142” дня между двумя датами – 19 июня 1989 года и 26 марта 2017 года.
Загрузка...