Учебное пособие по Excel: расчет даты и времени (вычисление разницы, возраста, сложение/вычитание)
В Excel часто используется вычисление даты и времени, например, вычисление разницы между двумя датами/временем, добавление или вычитание даты и времени, получение возраста на основе даты рождения и так далее. Здесь, в этом руководстве, перечислены почти сценарии расчета даты и времени и предоставлены соответствующие методы для вас.
В этом уроке я создаю несколько примеров для объяснения методов, вы можете изменить ссылки по своему усмотрению, когда используете приведенный ниже код или формулы VBA.
1. Рассчитать разницу между двумя датами/временем
Вычисление разницы между двумя датами или двумя временами может быть самой обычной проблемой вычисления даты и времени, с которой вы сталкиваетесь в повседневной работе с Excel. Приведенные ниже примеры могут помочь вам повысить эффективность, когда вы столкнетесь с теми же проблемами.
1.11 Расчет разницы между двумя датами в днях/месяцах/годах/неделях
Функцию 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 ключ для получения результата.
Внимание: если вы хотите рассчитать разницу между будущей датой и сегодняшней, измените start_date на сегодняшнюю и возьмите будущую дату как end_date следующим образом:
=DATEDIF(TODAY(),A14,"d")
Обратите внимание, что начальная_дата должна быть меньше конечной_даты в функции РАЗНДАТ, в противном случае она вернется к #ЧИСЛО! значение ошибки.
1.16 Расчет рабочих дней с праздником или без него между двумя датами
Иногда вам может понадобиться подсчитать количество рабочих дней с праздниками или без них между двумя заданными датами.
В этой части вы будете использовать функцию ЧИСТРАБДНИ.МЕЖД:
Нажмите ЧИСТРАБДНИ.МЕЖДУ знать его аргументы и использование.
Считать рабочие дни с праздниками
Чтобы подсчитать рабочие дни с праздниками между двумя датами в ячейках A2 и B2, используйте следующую формулу:
=NETWORKDAYS.INTL(A2,B2)
Нажмите Enter ключ для получения результата.
Считать рабочие дни без праздников
Чтобы подсчитать рабочие дни с праздниками между двумя датами в ячейках A2 и B2 и исключить праздники в диапазоне D5:D9, используйте следующую формулу:
=ЧИСТРАБДНИ.МЕЖД(A5,B5,1,D5:D9)
Нажмите Enter ключ для получения результата.
Примечание:
В приведенных выше формулах суббота и воскресенье считаются выходными. Если у вас разные выходные дни, измените аргумент [выходные] по своему усмотрению.
1.17 Расчет выходных дней между двумя датами
Если вы хотите подсчитать количество выходных дней между двумя датами, вам могут помочь функции СУММПРОИЗВ или СУММ.
Чтобы подсчитать выходные (суббота и воскресенье) между двумя датами в ячейках A12 и B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=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 содержатся значения start_time и end_time отдельно, используя следующие формулы:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Нажмите Enter ключ для получения результата.
Примечание:
- Если вы используете end_time-start_time, вы можете отформатировать результат в другой формат времени, как вам нужно, в диалоговом окне «Формат ячеек».
- Если вы используете ТЕКСТ(конец_время-первое_время,"формат_времени"), введите формат времени, в котором вы хотите, чтобы результат отображался в формуле, например, ТЕКСТ(конец_время-первое_время,"ч") возвращает 16.
- Если end_time меньше start_time, обе формулы возвращают значения ошибок. Для решения этой проблемы вы можете добавить ABS перед этими формулами, например ABS(B2-A2), ABS(ТЕКСТ(B2-A2,"чч:мм:сс")), а затем отформатировать результат как время.
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 может быстро получить разницу в часах между этими двумя временами.
Нажмите ЧАС для получения более подробной информации об этой функции.
Чтобы получить разницу в часах между временем в ячейках A14 и B14, используйте функцию HOUR следующим образом:
=HOUR(B14-A14)
Нажмите Enter ключ для получения результата.
Начальное_время должно быть меньше конечного_времени, в противном случае формула возвращает #ЧИСЛО! значение ошибки.
1.24 Рассчитывать разницу в минутах только между двумя таймами (не более 60 минут)
Функция МИНУТЫ может быстро получить разницу в минутах между этими двумя значениями времени и игнорировать часы и секунды.
Нажмите ПУТЕВКИ для получения более подробной информации об этой функции.
Чтобы получить только разницу в минутах между временем в ячейках A17 и B17, используйте функцию МИНУТЫ следующим образом:
=MINUTE(B17-A17)
Нажмите Enter ключ для получения результата.
Начальное_время должно быть меньше конечного_времени, в противном случае формула возвращает #ЧИСЛО! значение ошибки.
1.25 Рассчитывать разницу в секундах только между двумя временами (не более 60 секунд)
Функция SECOND может быстро получить разницу в секундах между этими двумя значениями времени и игнорировать часы и минуты.
Нажмите ВТОРОЙ для получения более подробной информации об этой функции.
Чтобы получить только разницу в секундах между временем в ячейках A20 и B20, используйте функцию SECOND следующим образом:
=SECOND(B20-A20)
Нажмите Enter ключ для получения результата.
Начальное_время должно быть меньше конечного_времени, в противном случае формула возвращает #ЧИСЛО! значение ошибки.
1.26 Вычислить разницу между двумя значениями времени и вернуть часы, минуты, секунды
Если вы хотите показать разницу между двумя временами в виде xx часов xx минут xx секунд, используйте функцию TEXT, как показано ниже:
Нажмите ТЕКСТ чтобы реализовать аргументы и использование этой функции.
Чтобы вычислить разницу между временем в ячейках A23 и B23, используйте следующую формулу:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Нажмите Enter ключ для получения результата.
Примечание:
Эта формула также вычисляет только разницу в часах, не превышающую 24 часа, а конечное_время должно быть больше, чем начальное_время, иначе возвращается #ЗНАЧ! значение ошибки.
1.27 Вычислить разницу между двумя датами и временем
Если есть два времени в формате мм/дд/гггг чч:мм:сс, для вычисления разницы между ними вы можете использовать одну из приведенных ниже формул, как вам нужно.
Получить разницу во времени между двумя датами и вернуть результат в формате чч: мм: сс
Возьмите две даты и времени в ячейках A2 и B2 в качестве экземпляра, используйте следующую формулу:
=B2-A2
Нажмите Enter ключ, возвращающий результат в формате даты и времени, затем отформатируйте этот результат как [Ч]: мм: сс в пользовательской категории под Номер регистрации Вкладка Формат ячеек Диалог.
Получить разницу между двумя датами и днями возврата, часами, минутами, секундами
Возьмите две даты и времени в ячейках A5 и B5 в качестве экземпляра, используйте следующую формулу:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Нажмите Enter ключ для получения результата.
Примечание: в обеих формулах end_datetime должно быть больше start_datetime, иначе формулы возвращают ошибочные значения.
1.28 Расчет разницы во времени в миллисекундах
Во-первых, вам нужно знать, как форматировать ячейку, чтобы отображались миллисекунды:
Выберите ячейки, которые вы хотите показать в миллисекундах, и справа, чтобы выбрать Формат ячеек для Формат ячеек диалоговое окно, выберите На заказ в Категории список на вкладке «Число» и введите это чч: мм: сс.000 в текстовое поле.
Используйте формулу:
Здесь, чтобы вычислить разницу между двумя значениями времени в ячейках A8 и B8, используйте следующую формулу:
=ABS(B8-A8)
Нажмите Enter ключ для получения результата.
1.29 Расчет рабочего времени между двумя датами, исключая выходные
Иногда может потребоваться подсчет рабочего времени между двумя датами, за исключением выходных (суббота и воскресенье).
Здесь рабочее время составляет 8 часов каждый день, и для расчета рабочего времени между двумя датами, указанными в ячейках A16 и B16, используйте следующую формулу:
=NETWORKDAYS(A16,B16) * 8
Нажмите Enter key, а затем отформатируйте результат как общий или числовой.
Дополнительные примеры расчета рабочего времени между двумя датами см. на странице Получите количество рабочих часов между двумя датами в Excel
Если вы только что Kutools for Excel установленный в Excel, 90 процентов расчетов разницы между датой и временем могут быть быстро решены без запоминания каких-либо формул.
1.31 Вычислить разницу между двумя датами с помощью Data & Time Helper
Чтобы вычислить разницу между двумя датами и временем в Excel, просто Помощник по дате и времени достаточно.
1. Выберите ячейку, в которую вы поместите вычисленный результат, и нажмите Кутулс > Формула Помощник > Помощник по дате и времени.
2. Во всплывающем Помощник по дате и времени диалоговое окно, выполните следующие настройки:
- Проверьте разница вариант;
- Выберите дату начала и время окончания в Ввод аргументов раздел, вы также можете напрямую ввести дату и время вручную в поле ввода или щелкнуть значок календаря, чтобы выбрать дату;
- Выберите тип выходного результата из выпадающего списка;
- Предварительный просмотр результата в Результат .
3. Нажмите Ok. Рассчитанный результат выводится, и перетащите маркер автозаполнения по ячейкам, которые вам также нужно вычислить.
Наконечник:
Если вы хотите получить разницу между двумя датами и показать результат в виде дней, часов и минут с помощью Kutools for Excel, сделайте следующее:
Выберите ячейку, в которую вы хотите поместить результат, и нажмите Кутулс > Формула Помощник > Дата и время > Подсчет дней, часов и минут между двумя датами.
Затем в Помощник по формулам укажите дату начала и дату окончания, затем нажмите Ok.
И результат разницы будет показан в виде дней, часов и минут.
Нажмите Помощник по дате и времени чтобы узнать больше об использовании этой функции.
Нажмите Kutools for Excel чтобы узнать все возможности этой надстройки.
Нажмите Бесплатная загрузка чтобы получить 30-дневную бесплатную пробную версию Kutools for Excel
Если вы хотите быстро подсчитать выходные, рабочие дни или определенный будний день между двумя датами, Kutools for Excel's Формула Помощник группа может вам помочь.
1. Выберите ячейку, в которую будет помещен рассчитанный результат, нажмите Кутулс > Статистический > Количество нерабочих дней между двумя датами/Количество рабочих дней между двумя датами/Подсчитайте номер определенного дня недели.
2. В выскакивающем Помощник по формулам диалоговое окно, укажите дату начала и дату окончания, если вы применяете Подсчитайте количество определенного дня недели, также необходимо указать день недели.
Чтобы подсчитать конкретный день недели, вы можете обратиться к примечанию, чтобы использовать 1-7 для обозначения воскресенья-субботы.
3. Нажмите Ok, а затем перетащите дескриптор автозаполнения на ячейки, в которых необходимо подсчитать количество выходных/рабочих дней/конкретных дней недели, если это необходимо.
Нажмите Kutools for Excel чтобы узнать все возможности этой надстройки.
Нажмите Бесплатная загрузка чтобы получить 30-дневную бесплатную пробную версию Kutools for Excel
2. Добавить или вычесть дату и время
За исключением вычисления разницы между двумя датами и временем, добавление или вычитание также является обычным вычислением даты и времени в Excel. Например, вы можете захотеть получить срок, основанный на дате производства и количестве дней хранения продукта.
2.11 Добавление или вычитание дней из даты
Чтобы добавить или вычесть определенное количество дней из даты, есть два разных метода.
Предположим, что к дате в ячейке A21 нужно добавить 2 день, выберите один из приведенных ниже способов, чтобы решить эту проблему.
Способ 1 дата+дни
Выберите ячейку и введите формулу:
=A+21
Нажмите Enter ключ для получения результата.
Если вы хотите вычесть 21 день, вместо знака плюса (+) выступает знак минуса (-).
Способ 2 Специальная вставка
1. Введите количество дней, которое вы хотите добавить в ячейку, предположим, в ячейке C2, а затем нажмите Ctrl + C чтобы скопировать его.
2. Затем выберите даты, к которым вы хотите добавить 21 день, щелкните правой кнопкой мыши, чтобы отобразить контекстное меню, и выберите Вставить специальный....
3. в Специальная вставка диалог, проверьте Добавить опция (Если вы хотите вычесть дни, отметьте вычитать вариант). Нажмите OK.
4. Теперь исходные даты меняются на 5-значные числа, отформатируйте их как даты.
2.12 Добавление или вычитание месяцев к дате
Чтобы добавить или вычесть месяцы из даты, можно использовать функцию ДАТАМЕС.
Нажмите EDATE изучить его аргументы и использование.
Предположим, что к дате в ячейке A6 добавлено 2 месяцев, используйте следующую формулу:
=EDATE(A2,6)
Нажмите Enter ключ для получения результата.
Если вы хотите вычесть 6 месяцев из даты, измените 6 на -6.
2.13 Добавить или вычесть годы к дате
Чтобы добавить или вычесть n лет к дате, можно использовать формулу, которая объединяет функции ДАТА, ГОД, МЕСЯЦ и ДЕНЬ.
Предположим, что к дате в ячейке A3 добавлено 2 года, используйте следующую формулу:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Нажмите Enter ключ для получения результата.
Если вы хотите вычесть 3 года из даты, измените 3 на -3.
2.14 Добавление или вычитание недель к дате
Для прибавления или вычитания недель к дате используется общая формула:
Предположим, что к дате в ячейке A4 добавлено 2 недели, используйте следующую формулу:
=A2+4*7
Нажмите Enter ключ для получения результата.
Если вы хотите вычесть 4 недели из даты, измените знак плюс (+) на знак минус (-).
2.15 Добавление или вычитание рабочих дней, включая или исключая праздничные дни
В этом разделе рассказывается, как использовать функцию РАБДЕНЬ для прибавления или вычитания рабочих дней к заданной дате, исключая праздничные дни или включая праздничные дни.
Войти WORKDAY чтобы узнать больше о его аргументах и использовании.
Добавить рабочие дни, включая праздники
В ячейке A2 указана дата, которую вы используете, в ячейке B2 содержится количество дней, которые вы хотите добавить, используйте следующую формулу:
=WORKDAY(A2,B2)
Нажмите Enter ключ для получения результата.
Добавить рабочие дни, исключая праздники
В ячейке A5 указана дата, которую вы используете, в ячейке B5 указано количество дней, которые вы хотите добавить, в диапазоне D5:D8 перечислены праздники, используйте следующую формулу:
=WORKDAY(A5,B5,D5:D8)
Нажмите Enter ключ для получения результата.
Примечание:
Функция РАБДЕНЬ принимает субботу и воскресенье как выходные, если ваши выходные выпадают на субботу и воскресенье, вы можете применить функцию РАБДЕНЬ.МЕЖД, которая поддерживает указание выходных.
Войти РАБДЕНЬ.МЕЖДУНАРОДНЫЙ Больше подробностей.
Если вы хотите вычесть рабочие дни из даты, просто измените число дней на отрицательное в формуле.
2.16 Добавление или вычитание определенного года, месяца, дня к дате
Если вы хотите добавить конкретный год, дни месяца к дате, формула, которая объединяет функции ДАТА, ГОД, МЕСЯЦ и ДНИ, может оказать вам услугу.
Чтобы добавить 1 год 2 месяца и 30 дней к дате в A11, используйте следующую формулу:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Нажмите Enter ключ для получения результата.
Если вы хотите вычесть, замените все знаки плюс (+) на знак минус (-).
2.21 Добавление или вычитание часов/минут/секунд к дате/времени
Здесь приведены некоторые формулы для прибавления или вычитания часов, минут или секунд к дате и времени.
Добавить или вычесть часы к дате и времени
Предположим, что к дате и времени (также может быть время) в ячейке A3 добавить 2 часа, используйте следующую формулу:
=A2+3/24
Нажмите Enter ключ для получения результата.
Добавить или вычесть часы к дате и времени
Предположим, что к дате и времени (также может быть время) в ячейке A15 добавить 5 минут, используйте следующую формулу:
=A2+15/1440
Нажмите Enter ключ для получения результата.
Добавить или вычесть часы к дате и времени
Предположим, что к дате и времени (также может быть время) в ячейке A20 добавить 8 секунд, используйте следующую формулу:
=A2+20/86400
Нажмите Enter ключ для получения результата.
2.22 Суммарное время за 24 часа
Предположим, что есть таблица Excel, в которой записано рабочее время всех сотрудников за неделю, чтобы суммировать общее рабочее время для расчета платежей, вы можете использовать СУММ(диапазон) чтобы получить результат. Но в целом суммарный результат будет отображаться как время, не превышающее 24 часов, как показано на скриншоте ниже, как можно получить правильный результат?
На самом деле вам просто нужно отформатировать результат как [чч]:мм:сс.
Щелкните правой кнопкой мыши ячейку результата, выберите Формат ячеек в контекстном меню и во всплывающем Формат ячеек диалога, выберите На заказ из блиста и введите [чч]:мм:сс в текстовое поле в правой части, нажмите 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 for Excel, только один инструмент — Дата и время Helper может решить большинство вычислений при добавлении и вычитании даты и времени.
1. Щелкните ячейку, в которую вы хотите вывести результат, и примените этот инструмент, щелкнув Кутулс > Формула Помощник > Помощник по дате и времени.
2. в Помощник по дате и времени диалог, проверьте Добавить Вариант или вычитать вариант, который вам нужен, затем выберите ячейку или непосредственно введите дату и время, которое вы хотите использовать в Ввод аргументов раздел, затем укажите годы, месяцы, недели, дни, часы, минуты и секунды, которые вы хотите добавить или вычесть, затем нажмите Ok. Смотрите скриншот:
Вы можете предварительно просмотреть рассчитанный результат в Результат .
Теперь результат выводится, перетащите авторучку на другие ячейки, чтобы получить результаты.
Нажмите Помощник по дате и времени чтобы узнать больше об использовании этой функции.
Нажмите Kutools for Excel чтобы узнать все возможности этой надстройки.
Нажмите Платная загрузка чтобы получить 30-дневную бесплатную пробную версию Kutools for Excel
2.41 Проверьте или выделите, если дата просрочена
Если есть список продуктов с истекшим сроком годности, вы можете проверить и выделить даты, срок действия которых истек на сегодняшний день, как показано на снимке экрана ниже.
Фактически, Условное форматирование может быстро справиться с этой работой.
1. Выберите даты, которые вы хотите проверить, затем нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел и введите =В2 в поле ввода (B2 — это первая дата, которую вы хотите проверить) и нажмите Формат всплывать Формат ячеек диалоговое окно, затем выберите другое форматирование, чтобы выделить даты истечения срока действия, как вам нужно. Нажмите OK > OK.
2.42 Возвращает конец текущего месяца/первый день следующего месяца/a>
Даты истечения срока действия некоторых продуктов приходятся на конец месяца производства или первый день следующего месяца производства, чтобы быстро перечислить даты истечения срока действия на основе даты производства, следуйте этой части.
Получить конец текущего месяца
Вот дата производства в ячейке B13, используйте следующую формулу:
=EOMONTH(B13,0)
Нажмите Enter ключ для получения результата.
Получить 1 числа следующего месяца
Вот дата производства в ячейке B18, используйте следующую формулу:
=EOMONTH(B18,0)+1
Нажмите Enter ключ для получения результата.
3. Рассчитать возраст
В этом разделе перечислены методы решения вопроса о том, как рассчитать возраст на основе заданной даты или серийного номера.
3.11 Расчет возраста по дате рождения
Получить возраст в десятичном числе на основе даты рождения
Нажмите YEARFRAC для получения подробной информации о его аргументах и использовании.
Например, чтобы получить возраст на основе списка дат рождения в столбце B2:B9, используйте следующую формулу:
=YEARFRAC(B2,TODAY())
Нажмите Enter , затем перетащите маркер автозаполнения вниз, пока не будут рассчитаны все возрасты.
Наконечник:
1) Вы можете указать десятичный знак, как вам нужно в Формат ячеек Диалог.
2) Если вы хотите рассчитать возраст на определенную дату на основе заданной даты рождения, измените СЕГОДНЯ() на конкретную дату, заключенную в двойные кавычки, например =ДОЛЯГОД(B2,"1/1/2021")
3) Если вы хотите получить возраст в следующем году на основе даты рождения, просто добавьте 1 в формулу, например =ДОЛЯГОД(B2,СЕГОДНЯ())+1.
Получить возраст целым числом на основе даты рождения
Нажмите РАЗНДАТ для получения подробной информации о его аргументах и использовании.
Используя приведенный выше пример, чтобы получить возраст на основе дат рождения в списке в B2: B9, используйте следующую формулу:
=DATEDIF(B2,TODAY(),"y")
Нажмите Enter , затем перетащите маркер автозаполнения вниз, пока не будут рассчитаны все возрасты.
Наконечник:
1) Если вы хотите рассчитать возраст на определенную дату на основе заданной даты рождения, измените СЕГОДНЯ() на конкретную дату, заключенную в двойные кавычки, например =DATEDIF(B2,"1/1/2021","г") .
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 чтобы получить возраст, затем перетащите маркер автозаполнения вниз к другим ячейкам.
Наконечник:
Если вы хотите рассчитать возраст в определенную дату на основе заданной даты рождения, замените СЕГОДНЯ() на конкретную дату, заключенную в двойные кавычки, например = =РАЗНДАТ(B12,"1/1/2021","Г")& " Годы, "&РАЗНДАТ(B12,"1/1/2021","ГМ")&" Месяцы, "&DATEIF(B12,"1/1/2021","MD")&" Дни".
3.13 Расчет возраста по дате рождения до 1
В Excel дату до 1 нельзя ввести как дату и время или правильно рассчитать. Но если вы хотите рассчитать возраст известного человека на основе заданной даты рождения (до 1/1900) и даты смерти, вам может помочь только код VBA.
1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно и щелкните Вставить и выберите Модули для создания нового модуля.
2. Затем скопируйте и вставьте приведенный ниже код в новый модуль.
VBA: рассчитать возраст до 1
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(дата рождения,дата смерти), в данном случае =ВозрастФунк(B22,C22), нажмите клавишу Enter, чтобы получить возраст. И используйте дескриптор автозаполнения, чтобы применить эту формулу к другим ячейкам, если это необходимо.
Если вы только что Kutools for Excel установленный в Excel, вы можете применить Помощник по дате и времени инструмент для расчета возраста.
1. Выберите ячейку, в которую вы хотите поместить рассчитанный возраст, и нажмите Кутулс > Формула Помощник > Помощник по дате и времени.
2. в Помощник по дате и времени диалог
- 1) Проверить Возраст вариант;
- 2) Выберите ячейку даты рождения или введите дату рождения напрямую или щелкните значок календаря, чтобы выбрать дату рождения;
- 3) Выберите Cегодня вариант, если вы хотите рассчитать текущий возраст, выберите Указанная дата вариант и введите дату, если вы хотите рассчитать возраст в прошлом или будущем;
- 4) Укажите тип вывода из выпадающего списка;
- 5) Предварительный просмотр результата вывода. Нажмите Ok.
Нажмите Помощник по дате и времени чтобы узнать больше об использовании этой функции.
Нажмите Kutools for Excel чтобы узнать все возможности этой надстройки.
Нажмите Бесплатная загрузка чтобы получить 30-дневную бесплатную пробную версию Kutools for Excel
3.31 Получить день рождения по идентификационному номеру
Если есть список идентификационных номеров, в которых для записи даты рождения используются первые 6 цифр, например, 920315330 означает, что дата рождения — 03, как можно быстро ввести дату рождения в другой столбец?
Теперь давайте возьмем в качестве примера список идентификационных номеров, начинающихся в ячейке C2, и воспользуемся следующей формулой:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Нажмите Enter ключ. Затем перетащите маркер автозаполнения вниз, чтобы получить другие результаты.
Примечание:
В формуле вы можете изменить ссылку на вашу потребность. Например, если идентификационный номер показан как 13219920420392, а день рождения — 04, вы можете изменить формулу на =MID(C20)&"/"&MID(C1992)&"/ "&MID(C2,8,2), чтобы получить правильный результат.
3.32 Расчет возраста по идентификационному номеру
Если есть список идентификационных номеров, в которых для записи даты рождения используются первые 6 цифр, например, 920315330 означает, что дата рождения — 03, как можно быстро вычислить возраст на основе каждого идентификационного номера в 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:
Объединение нескольких рабочих книг/листов в одну
Это руководство, в котором перечислены почти все сценарии объединения, с которыми вы можете столкнуться, и предоставлены относительно профессиональные решения для вас.
Разделить текст, число и ячейки даты (разделить на несколько столбцов)
Этот учебник разделен на три части: разделенные текстовые ячейки, разделенные числовые ячейки и разделенные ячейки даты. В каждой части представлены разные примеры, которые помогут вам понять, как справиться с задачей разделения при возникновении одной и той же проблемы.
Объедините содержимое нескольких ячеек без потери данных в Excel
Этот учебник сужает извлечение до определенной позиции в ячейке и собирает различные методы, помогающие извлекать текст или числа из ячейки по определенной позиции в Excel.
Сравните два столбца на совпадения и различия в Excel
Здесь эта статья охватывает большинство возможных сценариев сравнения двух столбцов, которые вы можете встретить, и надеюсь, что она может вам помочь.
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
- Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
