Разница дат в Excel
Обновлено: 21.11.2024
Вычисление разницы между двумя датами в Excel
Если нужно узнать количество дней между двумя датами, это очень легко сделать в Excel. Просто вычтите одну дату из другой. Например, если в ячейке A2 указана дата счета-фактуры 01.01.2015, а в ячейке B2 указана дата оплаты 30.01.2015, то вы можете ввести формулу =B2-A2, чтобы получить количество дней между две даты или 29. Excel может автоматически отформатировать ячейку с формулой в ней как дату, что означает, что вместо 29 вы увидите 29.01.1900. Но все, что вам нужно сделать, это установить для этой ячейки форматирование «Общее» или любой числовой формат (щелкните правой кнопкой мыши > выберите «Форматирование»), чтобы результат отображался как 29.
Если вы хотите узнать количество месяцев или лет между двумя датами, все становится сложнее. Лучше всего использовать функцию РАЗНДАТ(). Вы не найдете его в мастере функций, поэтому вам придется помнить, как вводить его самостоятельно. Объяснение, которое я слышал, заключается в том, что это не стандартная функция Excel, она включена только для обратной совместимости с Lotus 1-2-3.
Формат функции: РАЗНДАТ(дата начала,дата окончания,единица измерения).
Дата начала и дата окончания говорят сами за себя. Единица, означающая единицу измерения, может быть «D», «M» или «Y» для дня, месяца или года соответственно. Есть 3 дополнительных необычных параметра, которые объясняются в примере ниже.
В приведенной ниже таблице показаны примеры формул с использованием каждого из параметров единиц измерения. Столбец D показывает формулу, которая фактически использовалась в столбце C для получения результата, показанного в столбце C. Обратите внимание, что ответ показывает только полные единицы. Например, формула в строке 4 показывает 5 лет, хотя между двумя датами чуть больше 5 лет. Функция не округляется. Таким образом, если бы между двумя датами было 5 лет и 364 дня, ответ все равно был бы 5.
| A | B | C | D | E | |
| 1 | ДАТА НАЧАЛА | ДАТА ОКОНЧАНИЯ | РАЗНИЦА | ФОРМУЛА | ЕДИНИЦА – РАЗНИЦА, ВЫЧИСЛЕННАЯ В.. |
| 2 | 01/01/2010 | 18/02/ 2015 | 1874 | =DATEDIF(A2,B2,D") | Дней (дает тот же результат, что и =B2-A2) |
| 3 | 61 | =DATEDIF(A2,B2,"M") | Месяцы | ||
| 4 | 5 | =DATEDIF(A2,B2,"Y") | Годы | ||
| 5 | 17 | =DATEDIF(A2,B2,"MD") | Дни без учета месяца и года (дни с 1-го по 18-е) | ||
| 6 | 1 | =DATEDIF(A2,B2"YM") | месяцы, без учета года (месяцы с января по февраль) | ||
| 7 | 48 | =DATEDIF(A2,B2,"YD") | дни без учета года (дни с 1 января по 18 февраля) |
Если вам понравился этот пост:
Эта запись была опубликована 19 февраля 2015 г. в 10:33 и размещена в разделе Все, Общие советы. Вы можете следить за любыми ответами на эту запись через RSS 2.0.И комментарии, и проверка связи в настоящее время закрыты.
Используйте функцию 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,MyHolidays).
Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например 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). Выберите Пользовательский. В поле Тип введите [ч]:мм.
Рассчитать количество дней между двумя датами в Excel
Использование формулы вычитания для расчета количества дней между двумя датами
Если вы хотите рассчитать количество дней между двумя датами Excel, это можно сделать, просто вычитая более раннюю дату из более поздней даты.
Например, если дата начала хранится в ячейке B1, а дата окончания — в ячейке B2, количество дней между этими двумя датами рассчитывается путем ввода следующего в любую ячейку Excel:
Этот метод работает, потому что даты Excel хранятся внутри как простые целые числа. Только форматирование ячейки Excel приводит к тому, что ее значение отображается как дата, а не как простое число.
Это показано в примере ниже.
Пример использования формулы вычитания для расчета количества дней между двумя датами в Excel
В ячейке B3 следующей таблицы показан простой расчет разницы дат в Excel. При вычислении просто вычитается дата в ячейке B1 из даты в ячейке B2.
Приведенная выше формула вычитания возвращает результат 62.
Т.е. Между датами 01.01.2016 и 03.03.2016 62 дня.
Используйте функцию Excel Days для расчета количества дней между двумя датами
Функция Excel Days также вычисляет количество дней между двумя указанными датами. Однако эта функция появилась только в Excel 2013 и недоступна в более ранних версиях Excel.
Синтаксис функции Days:
где аргументы end_date и start_date — это две даты, между которыми вы хотите вычислить разницу.
Пример функции Excel Days
В ячейке B3 следующей электронной таблицы показана функция Excel Days, используемая для расчета количества дней между двумя датами в ячейках B1 и B2:
Приведенная выше функция Days возвращает значение 62.
Т.е. Между датами 01.01.2016 и 03.03.2016 62 дня.
Рассчитать количество лет между двумя датами в Excel
Функция Excel Yearfrac вычисляет количество лет между двумя указанными датами. Результат возвращается в виде десятичного значения.
Пример функции Excel Yearfrac
В ячейке B3 следующей электронной таблицы показана функция Excel Yearfrac, используемая для расчета количества лет между двумя датами в ячейках B1 и B2:
Приведенная выше функция Yearfrac возвращает значение 33,17222222.
Т.е. Между датами 01.01.1983 и 03.03.2016 – 33,17 года.
Функция Yearfrac также полезна, если вы хотите рассчитать возраст в Excel.
В этом руководстве показано, как рассчитать разницу между двумя датами в Excel. В конце вы сможете удобно использовать формулы Excel, чтобы вычислить точное количество лет, месяцев и дней, прошедших между двумя датами.
Давайте углубимся в это!
См. видеоруководство и расшифровку ниже:
Из нескольких вариантов мы рассмотрим самый простой и быстрый, чтобы научиться вычислять разницу между двумя датами.
Вычисление разницы между двумя датами в Excel с помощью функции «DATE-DIF»
Наиболее подходящей функцией для такого рода вычислений является функция «DATE-DIF». Мы сразу рассмотрим, как это работает.
Сначала щелкните целевую ячейку, в которой вы хотите отобразить результат. Введите знак равенства и продолжайте вводить функцию «DATE-DIF». Затем введите нужные данные в правильном порядке в скобки.
Начните с «Даты начала», введите запятую и закончите «Датой окончания».
Но мы еще не закончили. Введите еще одну запятую и сразу после нее добавьте «Y», что означает «годы». Это нужно сделать, чтобы показать, что нам нужно точное количество полных лет, прошедших между двумя выбранными датами.
Закройте скобки, нажмите «Enter» и та-да — вот и результат!
Как скопировать функцию «DATE-DIF» в другие строки таблицы данных
Эта функция может быть очень легко «переработана» в наших дальнейших расчетах. Нажмите в правом нижнем углу ячейки, содержащей формулу, и перетащите ее через все строки, где нам нужно вычислить количество лет. Идеально!
Excel подсчитал количество полных лет, прошедших между датами в каждой строке.
Как рассчитать, сколько месяцев прошло между двумя датами
Но нам все равно нужно подсчитать количество месяцев и дней.
Итак, давайте продолжим и посмотрим, сколько месяцев и дней прошло между двумя датами. Мы будем использовать ту же функцию, но нам нужно будет немного изменить ее.
Это небольшое изменение, которое нам нужно внести в формулу.
Снова нажмите на целевую ячейку и выполните шаги, которые вы только что видели: введите знак равенства, затем введите "DATE-DIF", затем "Дата начала", запятая, "Дата окончания", но теперь вместо «Y» введите «M» для «Месяцев», потому что мы хотим узнать количество полных месяцев.
Закройте скобки, и результат появится прямо перед нами, в целевой ячейке.
Нет более простого и быстрого способа подсчитать точное количество месяцев, прошедших между двумя датами!
Как рассчитать, сколько дней прошло между двумя датами
Как вы думаете, что входит в формулу для расчета количества полных дней?
Скоро вы узнаете, правильно ли вы угадали.
Начните как обычно: введите знак равенства, DATE-DIF, Start Date и End Date, разделенные запятой. И теперь мы закончим формулу с «D», что означает «Дни».
Закройте скобки, нажмите «Ввод».
Количество полных дней вычислить так же просто, как количество полных месяцев или лет.
Это универсальный способ вычисления разницы между двумя датами, работающий с любыми временными данными в таблице.
Вам интересно:
Если вы нашли это руководство полезным, поставьте нам лайк и посмотрите другие видеоуроки от EasyClick Academy. Узнайте, как использовать Excel быстро и легко!
Вы впервые пользуетесь EasyClick? Мы будем более чем рады приветствовать вас в нашем онлайн-сообществе. Нажмите кнопку «Подписаться» и присоединяйтесь к EasyClickers!
Читайте также: