Учебник Excel: Вычисления даты и времени (разница, возраст, сложение/вычитание)

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

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

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

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

1.11 Вычислить разницу между двумя датами в днях/месяцах/годах/неделях

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

Нажмите для подробной информации о функции 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, как показано ниже, чтобы получить целое число недель:

=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") &" лет, "&DATEDIF(A8, B8, "ym") &" месяцев, " & DATEDIF(A8, B8, "md") &" дней"

Нажмите Enter для получения результата.

A8 — начальная дата, B8 — конечная дата.

1.15 Вычислить разницу между датой и сегодняшним днем

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

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

Нажмите Enter для получения результата.

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

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

Обратите внимание, что в функции DATEDIF начальная дата должна быть меньше конечной, иначе будет возвращено значение ошибки #NUM!.

1.16 Вычислить рабочие дни с учетом или без учета праздников между двумя датами

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

В этом разделе используется функция NETWORKDAYS.INTL:

Нажмите NETWORKDAYS.INTL , чтобы узнать аргументы и применение.

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

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

=NETWORKDAYS.INTL(A2,B2)

Нажмите Enter для получения результата.

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

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

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

Нажмите Enter для получения результата.

Примечание:

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

1.17 Вычислить количество выходных между двумя датами

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

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

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

Или

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

Нажмите Enter для получения результата.

1.18 Вычислить количество определенного дня недели между двумя датами

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

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

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

Нажмите Enter для получения результата.

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

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

1.19 Вычислить количество оставшихся дней в месяце/году

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

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

Нажмите EOMONTH, чтобы узнать аргументы и применение.

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

Примечание:

• Если вы используете end_time-start_time, вы можете изменить формат результата на другой формат времени в диалоговом окне Формат ячеек.
• Если вы используете TEXT(end_time-first_time,"time_format"), укажите нужный формат времени прямо в формуле, например, TEXT(end_time-first_time,"h") вернет16.
• Если end_time меньше start_time, обе формулы вернут ошибку. Чтобы решить эту проблему, добавьте функцию ABS перед формулой, например, ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), затем отформатируйте результат как время.

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

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

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

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

=INT((B5-A5)*24)

Нажмите Enter и затем измените формат результата на общий или числовой.

Если вы хотите получить разницу в часах с десятичными, используйте (end_time-start_time)*24.

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

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

=INT((B8-A8)*1440)

Нажмите Enter и затем измените формат результата на общий или числовой.

Если вы хотите получить разницу в минутах с десятичными, используйте (end_time-start_time)*1440.

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

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

=(B11-A11)*86400)

Нажмите Enter и затем измените формат результата на общий или числовой.

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

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

Нажмите HOUR для подробной информации о функции.

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

=HOUR(B14-A14)

Нажмите Enter для получения результата.

Начальное время должно быть меньше конечного, иначе формула вернет ошибку #NUM!.

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

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

Нажмите MINUTE для подробной информации о функции.

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

=MINUTE(B17-A17)

Нажмите Enter для получения результата.

Начальное время должно быть меньше конечного, иначе формула вернет ошибку #NUM!.

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

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

Нажмите SECOND для подробной информации о функции.

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

=SECOND(B20-A20)

Нажмите Enter для получения результата.

Начальное время должно быть меньше конечного, иначе формула вернет ошибку #NUM!.

1.26 Вычислить разницу между двумя временами и получить часы, минуты, секунды

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

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

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

=TEXT(B23-A23,"h"" часов ""m"" минут ""s"" секунд"").

Нажмите Enter для получения результата.

Примечание:

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

1.27 Вычислить разницу между двумя датами и временем

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

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

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

=B2-A2

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

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

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

=INT(B5-A5) & " дней, " & HOUR(B5-A5) & " часов, " & MINUTE(B5-A5) & " минут, " & SECOND(B5-A5) & " секунд "

Нажмите Enter для получения результата.

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

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

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

Выделите ячейки, в которых нужно отобразить миллисекунды, и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек для открытия Формат ячеек диалогового окна, выберите Пользовательский в Категория списке на вкладке Число и введите hh:mm:ss.000 в текстовое поле.

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

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

=ABS(B8-A8)

Нажмите Enter для получения результата.

1.29 Вычислить рабочие часы между двумя датами без учета выходных

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

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

=NETWORKDAYS(A16,B16) *8

Нажмите Enter и затем измените формат результата на общий или числовой.

Для дополнительных примеров по расчету рабочих часов между двумя датами посетите Get Work Hours Between Two Dates In Excel

Если у вас установлен Kutools для 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. В Специальная вставка диалоговом окне отметьте Добавить (Если хотите вычесть дни, отметьте Вычесть опцию). Нажмите OK.

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

2.12 Прибавить или вычесть месяцы к дате

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

Нажмите EDATE, чтобы узнать аргументы и применение.

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

=EDATE(A2,6)

Нажмите Enter для получения результата.

Если нужно вычесть6 месяцев, замените6 на -6.

2.13 Прибавить или вычесть годы к дате

Чтобы прибавить или вычесть n лет к дате, используйте формулу, объединяющую функции DATE, YEAR, MONTH и DAY.

Предположим, нужно прибавить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 Прибавить или вычесть определенное количество лет, месяцев, дней к дате

Если нужно прибавить определенное количество лет, месяцев и дней к дате, используйте формулу, объединяющую функции DATE, YEAR, MONTH и DAYS.

Чтобы прибавить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(диапазон) для получения результата. Обычно итоговое значение будет показано как время, не превышающее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, большинство расчетов по прибавлению и вычитанию даты и времени можно выполнить одним инструментом — Дата и время — помощникрешит большинство задач по прибавлению и вычитанию даты и времени.

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

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

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

Теперь результат выведен, перетащите маркер автозаполнения на другие ячейки для получения результатов.

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

Нажмите Kutools для Excel, чтобы узнать обо всех возможностях этого дополнения.

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

2.41 Проверить или выделить, если дата просрочена

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

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

1. Выделите даты, которые хотите проверить, затем нажмите Главная > Условное форматирование > Новое правило.

2. В Новое правило форматирования диалоговом окне выберите Использовать формулу для определения форматируемых ячеек в Выбор типа правила разделе и введите =B2 в поле ввода (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) Если нужно вычислить возраст на определенную дату по дате рождения, замените 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")&" лет, "&DATEDIF(B12,TODAY(),"YM")&" месяцев, "&DATEDIF(B12,TODAY(),"MD")&" дней"

Нажмите 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/1/1900) и дате смерти, поможет только код 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, дата рождения —20.04.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 Затем перетащите маркер автозаполнения вниз для получения других результатов.

Примечание:

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

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

---

Лучшие инструменты для повышения производительности Office

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

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

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

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...