Руководство по Excel: расчёт даты и времени (разница, возраст, сложение/вычитание)

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

В этом руководстве приведены примеры, поясняющие методы работы. Используя приведённые ниже коды VBA или формулы, вы можете адаптировать ссылки под свои задачи.

1. Расчёт разницы между двумя датами или временными значениями

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

1,11 Расчёт разницы между двумя датами в днях/месяцах/годах/неделях

Функция DATEDIF в Excel позволяет быстро рассчитать разницу между двумя датами — в днях, месяцах, годах и даже неделях!

Подробнее о функции DATEDIF

Разница в днях между двумя датами

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

=DATEDIF(A2,B2,"d")

Нажмите клавишу Enter, чтобы получить результат.

Разница в месяцах между двумя датами

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

=DATEDIF(A5,B5,"m")

Нажмите клавишу Enter, чтобы получить результат.

Разница в годах между двумя датами

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

=DATEDIF(A8,B8,"y")

Нажмите клавишу Enter, чтобы получить результат.

Разница в неделях между двумя датами

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

=DATEDIF(A11,B11,"d")/7

Нажмите клавишу Enter, чтобы получить результат.

Примечание:

1) При использовании приведённой выше формулы для расчёта разницы в неделях результат может отображаться в формате даты. В этом случае измените формат ячейки с результатом на «Общий» или «Числовой» в зависимости от ваших потребностей.

2) При использовании приведённой выше формулы для расчёта разницы в неделях результат может быть десятичным числом. Если вам нужно целое значение номера недели, добавьте перед формулой функцию ОКРВНИЗ, как показано ниже, чтобы получить целое количество недель:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1,12 Вычислить количество месяцев между двумя датами, игнорируя годы и дни

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

=DATEDIF(A2,B2,"ym")

Нажмите клавишу Enter, чтобы получить результат.

A2 — это дата начала, а B2 — дата окончания.

1,13 Вычислить количество дней между двумя датами, игнорируя годы и месяцы

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

=DATEDIF(A5,B5,"md")

Нажмите клавишу Enter, чтобы получить результат.

A5 — это дата начала, а B5 — дата окончания.

1,14 Расчёт разницы между двумя датами с выводом лет, месяцев и дней

Если вы хотите получить разницу между двумя датами в формате «xx лет, xx месяцев и xx дней», как показано на скриншоте ниже, воспользуйтесь приведённой формулой.

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

Нажмите клавишу Enter, чтобы получить результат.

A8 — это дата начала, а B8 — дата окончания.

1,15 Расчёт разницы между датой и текущей датой

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

=DATEDIF(A11,TODAY(),"d")

Нажмите клавишу Enter, чтобы получить результат.

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

=DATEDIF(TODAY(),A14,"d")

Обратите внимание: в функции DATEDIF начальная дата должна быть раньше конечной, иначе вернётся ошибка #ЧИСЛО!

1,16 Расчёт рабочих дней с учётом или без учёта праздников между двумя датами

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

В этом разделе используется функция СЕТРАБДНИ.МЕЖД:[TN_179_END]]

Щелкните СЕТРАБДНИ.МЕЖД, чтобы узнать её аргументы и способы применения.

Подсчёт рабочих дней с учётом праздников

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

=NETWORKDAYS.INTL(A2,B2)

Нажмите клавишу Enter, чтобы получить результат.

Подсчёт рабочих дней без учёта праздников

Чтобы подсчитать рабочие дни между датами в ячейках A2 и B2, исключая праздничные дни из диапазона D5:D9, используйте следующую формулу:

=NETWORKDAYS.INTL(A5,B5,1,D5:D9)

Нажмите клавишу Enter, чтобы получить результат.

Примечание:

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

1,17 Подсчёт выходных дней между двумя датами

Если нужно подсчитать количество выходных дней между двумя датами, на помощь придут функции СУММПРОИЗВ или СУММ.

Чтобы подсчитать выходные дни (субботы и воскресенья) между датами в ячейках A12 и B12:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&,":"&,B12)),2)>,5))

Или

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

Нажмите клавишу Enter, чтобы получить результат.

1,18 Подсчёт определённого дня недели между двумя датами

Чтобы подсчитать, сколько раз встречается определённый день недели (например, понедельник) в заданном диапазоне дат, используйте комбинацию функций ЦЕЛОЕ и ДЕНЬНЕД.

Ячейки A15 и B15 содержат две даты, между которыми необходимо подсчитать количество понедельников. Воспользуйтесь следующей формулой:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

Нажмите клавишу Enter, чтобы получить результат.

Измените номер дня недели в функции ДЕНЬНЕД, чтобы подсчитать другой день недели:

1 — воскресенье, 2 — понедельник, 3 — вторник, 4 — среда, 5 — четверг, 6 — пятница и 7 — суббота)

1,19 Подсчёт Осталось дней в месяце/году

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

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

Щелкните КОНМЕСЯЦА, чтобы узнать аргументы и способы применения функции.

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

=EOMONTH(A2,0)-A2

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

Совет: результаты могут отображаться в формате даты; просто измените формат на «Общий» или «Числовой».

Получить Осталось дней текущего года

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

=DATE(YEAR(A2),12,31)-A2

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

1,21 Вычисление разницы между двумя временными значениями

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

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

=B2-A2

=TEXT(B2-A2,"hh:mm:ss")

Нажмите клавишу Enter, чтобы получить результат.

Примечание:

• Если вы используете формулу «конечное_время – начальное_время», результат можно отформатировать в нужный временной формат через диалоговое окно «Формат ячеек».
• Если вы используете формулу TEXT(время_окончания-время_начала;«формат_времени»), укажите желаемый формат времени для отображения результата в формуле; например, TEXT(время_окончания-время_начала;«ч») вернёт 16.
• Если конечное_время меньше начального_времени, обе формулы возвращают ошибку. Чтобы решить эту проблему, добавьте ABS перед этими формулами: например, ABS(B2-A2) или ABS(TEXT(B2-A2;«hh:mm:ss»)), а затем отформатируйте результат как время.

1,22 Вычисление разницы между двумя временными значениями в часах/минутах/секундах

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

Получение разницы в часах между двумя временными значениями

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

=INT((B5-A5)*24)

Нажмите клавишу Enter, а затем измените формат результата с временного на «Общий» или «Числовой».

Если вы хотите получить разницу во времени в десятичных часах, используйте формулу: (конечное_время – начальное_время) × 24.

Получение разницы в минутах между двумя временными значениями

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

=INT((B8-A8)*1440)

Нажмите клавишу Enter, затем измените формат результата с временного на «Общий» или «Числовой».

Если вам нужна разница в десятичных минутах, используйте формулу: (конечное_время – начальное_время) × 1440.

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

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

=(B11-A11)*86400)

Нажмите клавишу Enter, затем измените формат результата с временного на «Общий» или «Числовой».

1,23 Вычислить разницу только в часах между двумя временными значениями (не более 24 часов)

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

Щёлкните HOUR, чтобы узнать больше об этой функции.

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

=HOUR(B14-A14)

Нажмите клавишу Enter, чтобы получить результат.

Начальное время должно быть меньше конечного, иначе формула вернёт ошибку #ЧИСЛО!

1,24 Вычислить разницу только в минутах между двумя временными значениями (не более 60 минут)

Функция MINUTE мгновенно извлекает разницу в минутах между двумя временными значениями, игнорируя часы и секунды.

Щёлкните MINUTE, чтобы узнать больше об этой функции.

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

=MINUTE(B17-A17)

Нажмите клавишу Enter, чтобы получить результат.

Начальное время должно быть меньше конечного, иначе формула вернёт ошибку #ЧИСЛО!

1,25 Вычислить разницу только в секундах между двумя временными значениями (не более 60 секунд)

Функция SECOND мгновенно извлекает разницу в секундах между двумя временными значениями, игнорируя часы и минуты.

Щёлкните SECOND, чтобы узнать больше об этой функции.

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

=SECOND(B20-A20)

Нажмите клавишу Enter, чтобы получить результат.

Начальное время должно быть меньше конечного, иначе формула вернёт ошибку #ЧИСЛО!

1,26 Вычисление разницы между двумя временными значениями с отображением в формате «часы, минуты, секунды»

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

Щёлкните TEXT, чтобы узнать об аргументах и способах использования этой функции.

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

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

Нажмите клавишу Enter, чтобы получить результат.

Примечание:

Эта формула также вычисляет разницу в часах, не превышающую 24 часов, при условии что конечное время больше начального; в противном случае возвращается ошибка #ЗНАЧ!

1,27 Вычисление разницы между двумя датами со временем

Если у вас есть две даты в формате мм/дд/гггг чч:мм:сс и вы хотите рассчитать разницу между ними, воспользуйтесь одной из приведённых ниже формул — в зависимости от ваших задач.

Получение разницы между двумя датами со временем с результатом в формате чч:мм:сс

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

=B2-A2

Нажмите клавишу Enter, чтобы получить результат в формате даты со временем, затем задайте для него пользовательский формат [h]:mm:ss в категории Число на вкладке Установить формат ячейки диалогового окна.
 

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

Возьмём, к примеру, две даты со временем в ячейках A5 и B5. Воспользуйтесь следующей формулой:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

Нажмите клавишу Enter, чтобы получить результат.

Примечание: в обеих формулах конечная дата со временем должна быть позже начальной — иначе результат будет некорректным.

1,28 Вычислить разницу во времени с учётом миллисекунд

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

Выделите ячейки, в которых нужно отображать миллисекунды, щёлкните по ним правой кнопкой мыши и выберите Установить формат ячейки, чтобы открыть диалоговое окно Установить формат ячейки. На вкладке «Число» в списке Числовые форматы выберите категорию «Все форматы», а затем введите в поле ввода чч:мм:сс.000.

Используйте формулу:

Для вычисления разницы между двумя временными значениями в ячейках A8 и B8 используйте следующую формулу:

=ABS(B8-A8)

Нажмите клавишу Enter, чтобы получить результат.

1,29 Вычислить количество рабочих часов между двумя датами, исключая выходные дни

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

Предположим, что рабочий день составляет ровно 8 часов. Чтобы рассчитать количество рабочих часов между датами в ячейках A16 и B16, используйте следующую формулу:

=NETWORKDAYS(A16,B16) * 8

Нажмите клавишу Enter, а затем установите для результата формат «Общий» или «Числовой».

Дополнительные примеры расчёта рабочих часов между двумя датами см. в статье Расчёт рабочих часов между двумя датами в Excel.

Если у вас установлено расширение Kutools для Excel для Excel, 90 % задач по вычислению разницы дат и времени можно быстро решить — без запоминания формул!

1,31 Вычисление разницы между двумя датами со временем с помощью средства «Помощник по дате и времени»

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

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

2. В появившемся диалоговом окне Помощник по дате и времени выполните следующие настройки:

1. Установите флажок Разница;
2. Выберите начальную и конечную дату и время в разделе Ввод аргумента, либо введите дату и время вручную в поле ввода, либо щёлкните значок календаря для выбора даты;
3. Выберите Тип вывода результата из списка Раскрывающийся список;
4. Предварительный просмотр результата — в разделе Результат.

3. Нажмите кнопку ОК. Результат вычисления появится в ячейке — просто перетащите маркер автозаполнения на остальные ячейки, где нужно выполнить такой же расчёт.

Совет:

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

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

Затем в диалоговом окне Помощник формул укажите дату начала и конечную дату, после чего нажмите ОК.

Результат разницы будет отображён в виде дней, часов и минут.

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

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

Нажмите Бесплатная загрузка, чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel

1,32 Рассчитайте разницу в выходных днях, рабочих днях или определённых днях недели между двумя датами и временем с помощью Помощник формул

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

1. Выберите ячейку для размещения результата расчёта и нажмите Kutools > Статистические > Количество нерабочих дней между двумя датами / Количество рабочих дней между двумя датами / Количество дней недели между двумя датами.

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

Чтобы подсчитать определённый день недели, воспользуйтесь примечанием и используйте 1-7 для обозначения воскресенья–субботы.

3. Нажмите ОК, а затем, если нужно, перетащите маркер автозаполнения на ячейки, в которых требуется подсчитать количество выходных или рабочих дней, а также определённого дня недели.

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

Нажмите Бесплатная загрузка, чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel

2. Сложение и вычитание даты и времени

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

2,11 Прибавление или вычитание дней к дате

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

Допустим, нужно прибавить 21 день к дате в ячейке A2. Выберите один из приведённых ниже способов:

Метод 1 дата+дни

Выберите ячейку и введите формулу:

=A+21

Нажмите клавишу Enter, чтобы получить результат.

Если вы хотите вычесть 21 день, просто замените знак «плюс» (+) на знак «минус» (–).

Метод 2 Вставить специально

1. Введите в ячейку количество дней, которые нужно прибавить (например, C2), затем нажмите Ctrl+C, чтобы скопировать значение.

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

3. В диалоговом окне Вставить специально установите флажок Прибавить(если вы хотите вычесть дни, установите флажок)Вычесть). Нажмите ОК.

4. Теперь исходные даты преобразовались в пятисимвольные числа — отформатируйте их обратно в даты.

2,12 Прибавление или вычитание месяцев к дате

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

Нажмите ДАТАМЕС (EDATE), чтобы изучить её аргументы и способы применения.

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

=EDATE(A2,6)

Нажмите клавишу Enter, чтобы получить результат.

Если вы хотите вычесть 6 месяцев из даты, замените 6 на −6.

2,13 Прибавление или вычитание лет к дате

Для прибавления или вычитания N лет к дате можно использовать формулу, объединяющую функции ДАТА, ГОД, МЕСЯЦ и ДЕНЬ.

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

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

Нажмите клавишу Enter, чтобы получить результат.

Если вы хотите вычесть 3 года из даты, замените 3 на −3.

2,14 Прибавление или вычитание недель к дате

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

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

=A2+4*7

Нажмите клавишу Enter, чтобы получить результат.

Если вы хотите вычесть 4 недели из даты, просто замените знак «плюс» (+) на знак «минус» (–).

2,15 Прибавление или вычитание рабочих дней с учётом или без учёта праздников

В этом разделе описано, как использовать функцию РАБДЕНЬ (WORKDAY) для прибавления или вычитания рабочих дней к заданной дате с исключением или включением праздничных дней.

Перейдите на страницу РАБДЕНЬ (WORKDAY), чтобы подробнее узнать об аргументах и применении этой функции.

Прибавление рабочих дней с учётом праздников

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

=WORKDAY(A2,B2)

Нажмите клавишу Enter, чтобы получить результат.

Прибавление рабочих дней без учёта праздников

В ячейке A5 находится исходная дата, в ячейке B5 — количество дней, которые необходимо прибавить, а в диапазоне D5:D8 перечислены праздничные дни. Используйте следующую формулу:

=WORKDAY(A5,B5,D5:D8)

Нажмите клавишу Enter, чтобы получить результат.

Примечание:

Функция РАБДЕНЬ (WORKDAY) считает выходными субботу и воскресенье. Если ваши выходные приходятся именно на эти дни, вы можете использовать функцию РАБДЕНЬ.МЕЖД (WORKDAY.INTL), которая позволяет задавать собственные выходные дни.

Перейдите на страницу РАБДЕНЬ.МЕЖД (WORKDAY.INTL), чтобы узнать больше.

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

2,16 Прибавление или вычитание конкретных лет, месяцев и дней к дате

Если вы хотите прибавить определённое количество лет, месяцев и дней к дате, воспользуйтесь формулой, объединяющей функции ДАТА, ГОД, МЕСЯЦ и ДЕНЬ.

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

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

Нажмите клавишу Enter, чтобы получить результат.

Если вы хотите выполнить вычитание, замените все знаки «плюс» (+) на знаки «минус» (–).

2,21 Прибавление или вычитание часов/минут/секунд к дате и времени

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

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

Допустим, необходимо прибавить 3 часа к дате и времени (или просто ко времени) в ячейке A2. Используйте следующую формулу:

=A2+3/24

Нажмите клавишу Enter, чтобы получить результат.

Прибавление или вычитание минут к дате и времени

Допустим, необходимо прибавить 15 минут к дате и времени (или просто ко времени) в ячейке A5. Используйте следующую формулу:

=A2+15/1440

Нажмите клавишу Enter, чтобы получить результат.

Прибавление или вычитание секунд к дате и времени

Допустим, необходимо прибавить 20 секунд к дате и времени (или просто ко времени) в ячейке A8. Используйте следующую формулу:

=A2+20/86400

Нажмите клавишу Enter, чтобы получить результат.

2,22 Суммировать временные значения, превышающие 24 часа

Предположим, в Excel есть таблица с учётом рабочего времени всех сотрудников за неделю. Чтобы рассчитать общее рабочее время для начисления оплаты, вы можете использовать SUM(range) и получить результат. Однако обычно итог отображается как время, не превышающее 24 часов, как показано на скриншоте ниже. Как получить правильный результат?

На самом деле достаточно просто отформатировать результат как [hh]:mm:ss.

Щёлкните правой кнопкой мыши по ячейке с результатом и выберите в контекстном меню пункт Установить формат ячейки. В появившемся диалоговом окне Установить формат ячейки выберите в списке «Другие», а затем введите в текстовое поле справа [hh]:mm:ss и нажмите OK.
  

Итоговый результат будет отображён корректно.

2,23 Прибавить рабочие часы к дате, исключая выходные и праздничные дни

Здесь приведена длинная формула для расчёта даты окончания путём добавления заданного количества рабочих часов к дате начала с учётом исключения выходных (субботы и воскресенья) и праздничных дней.

В таблице Excel ячейка A11 содержит дату и время начала, B11 — количество рабочих часов, ячейки E11 и E13 указывают начало и окончание рабочего дня соответственно, а в ячейке E15 указан праздник, который необходимо исключить.

Используйте следующую формулу:

=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>, $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))

Нажмите клавишу Enter, чтобы получить результат.

Если у вас установлен Kutools для Excel, большинство операций по сложению и вычитанию дат и времени можно выполнить с помощью всего одного инструмента — Date & Time Helper.

1. Щёлкните по ячейке, в которую нужно вывести результат, и примените этот инструмент, последовательно выбрав Kutools>Помощник формул>Помощник по дате и времени.

2. В диалоговом окне Помощник по дате и времени установите флажок напротив нужного варианта — «Добавить» или «Вычесть», затем выберите ячейку или вручную введите дату и время в поле Ввод аргумента. После этого укажите количество лет, месяцев, недель, дней, часов, минут и секунд, которые нужно добавить или вычесть, и нажмите Ok. См. скриншот:

Рассчитанный результат можно просмотреть в разделе «Результат».

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

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

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

Нажмите Бесплатная загрузка, чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel

2,41 Проверка или выделение просроченных дат

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

На самом деле эту задачу быстро решает инструмент Использовать условное форматирование.

1. Выделите даты, которые нужно проверить, затем последовательно выберите Главная > Использовать условное форматирование > Создать правило.

2. В диалоговом окне «Создание правила форматирования» в разделе «Выбор типа правила» выберите вариант «Использовать формулу для определения форматируемых ячеек», а затем введите в поле ввода формулу =B2<,TODAY() (где B2 — первая дата, которую вы хотите проверить). Далее нажмите Формат, чтобы открыть диалоговое окно «Установить формат ячейки», и задайте нужное форматирование для выделения истёкших дат. Нажмите OK > OK.
  

2,42 Получение последнего дня текущего месяца / первого дня В следующем месяце

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

Получение последнего дня текущего месяца

В ячейке B13 указана дата производства. Используйте следующую формулу:

=EOMONTH(B13,0)

Нажмите клавишу Enter, чтобы получить результат.

Получение 1-го дня В следующем месяце

В ячейке B18 указана дата производства. Используйте следующую формулу:

=EOMONTH(B18,0)+1

Нажмите клавишу Enter, чтобы получить результат.

3. Расчёт возраста

В этом разделе описаны методы расчёта возраста по заданной дате или серийному номеру.

3,11 Расчёт возраста на основе заданной даты рождения

Получение возраста в виде десятичного числа на основе даты рождения

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

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

=YEARFRAC(B2,TODAY())

Нажмите клавишу Enter, а затем протяните маркер автозаполнения вниз, пока не рассчитаются все возрасты.

Совет:

1) В диалоговом окне Установить формат ячейки вы можете указать необходимое количество десятичных знаков.

2) Если вы хотите рассчитать возраст на конкретную дату на основе заданной даты рождения, change TODAY() укажите нужную дату в двойных кавычках, например: =YEARFRAC(B2,«1/1/2021»)

3) Если вы хотите рассчитать возраст на текущую дату на основе даты рождения, просто добавьте 1 к формуле, например: =YEARFRAC(B2,TODAY())+1.

Получение возраста в виде целого числа на основе даты рождения

Нажмите DATEDIF, чтобы узнать больше об аргументах и применении этой функции.

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

=DATEDIF(B2,TODAY(),"y")

Нажмите клавишу Enter, затем протяните маркер автозаполнения вниз, пока не рассчитаются все возрасты.

Совет:

1) Если вы хотите рассчитать возраст на конкретную дату на основе заданной даты рождения, замените TODAY() на нужную дату в двойных кавычках, например: =DATEDIF(B2,«1/1/2021»,«y»).

2) Если вы хотите рассчитать возраст на текущую дату на основе даты рождения, просто добавьте 1 к формуле, например: =DATEDIF(B2,TODAY(),«y»)+1.

3,12 Расчёт возраста в формате «лет, месяцев, дней» на основе даты рождения

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

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

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

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

Совет:

Если вы хотите рассчитать возраст на конкретную дату на основе заданной даты рождения, замените TODAY() на нужную дату в двойных кавычках, например: =DATEDIF(B12,«1/1/2021»,«Y»)&« лет, »&DATEDIF(B12,«1/1/2021»,«YM»)&« месяцев, »&DATEDIF(B12,«1/1/2021»,«MD»)&« дней».

3,13 Расчёт возраста по дате рождения до 1/1/1900

В Excel даты до 1/1/1900 нельзя вводить как дату/время или корректно рассчитывать. Однако если вам нужно рассчитать возраст известного человека на основе заданной даты рождения (до 1/11900) и даты смерти, вам поможет только код на VBA.

1. Нажмите клавиши Alt+F11, чтобы открыть окно Microsoft Visual Basic for Applications, перейдите на вкладку Вставка и выберите пункт Модуль, чтобы создать новый модуль.

2. Затем скопируйте приведённый ниже код и вставьте его в новый модуль.

VBA: Расчёт возраста до 1/1/1900

Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
    Dim xSMonth As Integer
    Dim xSDay As Integer
    Dim xSYear As Integer
    Dim xEMonth As Integer
    Dim xEDay As Integer
    Dim xEYear As Integer
    Dim xAge As Integer
    If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    xAge = xEYear - xSYear
    If xSMonth > xEMonth Then
        xAge = xAge - 1
    ElseIf xSMonth = xEMonth Then
        If xSDay > xEDay Then xAge = xAge - 1
    End If
    If xAge < 0 Then
        AgeFunc = "Invalid Date"
    Else
        AgeFunc = xAge
    End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
    Dim I As Long
    Dim K As Long
    Y = 0
    M = 0
    D = 0
    GetDate = True
    On Error Resume Next
    I = InStr(1, DateStr, "/")
    M = CLng(Left(DateStr, I - 1))
    D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
    Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
    If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
        GetDate = False
    End If
End Function

3. Сохраните код, вернитесь на лист, выберите ячейку для отображения рассчитанного возраста и введите =AgeFunc(birthdate,deathdate) — в данном случае =AgeFunc(B22,C22) — затем нажмите клавишу Enter, чтобы получить возраст. При необходимости воспользуйтесь маркером автозаполнения, чтобы применить эту формулу к другим ячейкам.

Если у вас установлено расширение Kutools для Excel в Excel, вы можете воспользоваться инструментом Помощник по дате и времени для расчёта возраста.

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

2. В диалоговом окне Помощник по дате и времени

• 1) Установите флажок Возраст;
• 2) Выберите ячейку с датой рождения, введите дату напрямую или щелкните значок календаря для выбора даты рождения;
• 3) Выберите параметр Сегодня, если хотите рассчитать текущий возраст; выберите Определенная датаи введите дату, если хотите рассчитать возраст в прошлом или будущем;
• 4) Укажите тип вывода в списке Раскрывающийся список;
• 5) Предварительный просмотр результата. Нажмите ОК.

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

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

Нажмите Бесплатная загрузка, чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel

3,31 Получение даты рождения из номера документа

Если у вас есть список номеров документов, где первые 6 цифр обозначают дату рождения (например, 920315330 соответствует дате рождения 15,03.1992), как быстро перенести эту дату в отдельный столбец?

Теперь возьмём в качестве примера список номеров документов, начинающийся с ячейки C2, и воспользуемся следующей формулой:

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

Нажмите клавишу Enter, а затем перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

Примечание:

Вы можете свободно изменять ссылки в формуле. Например, если номер документа — 13219920420392, а дата рождения — 04/20/1992, скорректируйте формулу на =MID(C2,8,2)&«/»&MID(C2,10,2)&«/»&MID(C2,4,4), чтобы получить правильный результат.

3,32 Расчёт возраста по номеру документа

Если в списке номеров документов первые 6 цифр обозначают дату рождения (например, 920315330 соответствует дате рождения 15,03.1992), как быстро рассчитать возраст по каждому такому номеру в Excel?

Теперь возьмём в качестве примера список номеров документов, начинающийся с ячейки C2, и воспользуемся следующей формулой:

=DATEDIF(DATE(IF(LEFT(C2,2)>,TEXT(TODAY(),"YY"),"19"&,LEFT(C2,2),"20"&,LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")

Нажмите клавишу EnterЗатем перетащите маркер автозаполнения вниз, чтобы получить остальные результаты.

Примечание:

В этой формуле, если год меньше текущего, он считается относящимся к XXI веку (например, 200203943 интерпретируется как 2020 год); если же год больше текущего — к XX веку (например, 920420392 интерпретируется как 1992 год).

Другие руководства по Excel:

---

Лучшие инструменты для повышения продуктивности в офисе

Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

• Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
• Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…