Урок за Excel: Изчисляване на дата и час (изчисляване на разлика, възраст, добавяне/изваждане)
В Excel изчисляването на дата и час се използва често, като например изчисляване на разликата между две дати/часове, добавяне или изваждане на дата и час, получаване на възраст въз основа на датата на раждане и т.н. Тук, в този урок, той изброява почти сценарии за изчисляване на дата и час и предоставя свързаните методи за вас.
В този урок създавам няколко примера, за да обясня методите, можете да промените препратките според нуждите си, когато използвате по-долу VBA код или формули
1. Изчислете разликата между две дати/часове
Изчисляването на разликата между две дати или два часа може да е най-нормалния проблем при изчисляване на дата и час, който срещате в ежедневната си работа с Excel. Следните примери по-долу могат да ви помогнат да подобрите ефективността, когато срещнете същите проблеми.
1.11 Изчислете разликата между две дати в дни/месеци/години/седмици
Функцията DATEDIF на Excel може да се използва за бързо изчисляване на разликата между две дати в дни, месеци, години и седмици.
Кликнете за повече подробности относно DATEDIF функция
Дни разлика между две дати
За да получите разликата в дните между две дати в клетка A2 и B2, моля, използвайте формулата като тази
=DATEDIF(A2,B2,"d")
Натискане Въведете ключ за получаване на резултата.
Разлика в месеци между две дати
За да получите разликата в месеците между две дати в клетка A5 и B5, моля, използвайте формулата като тази
=DATEDIF(A5,B5,"m")
Натискане Въведете ключ за получаване на резултата.
Години разлика между две дати
За да получите разликата в годините между две дати в клетка A8 и B8, моля, използвайте формулата като тази
=DATEDIF(A8,B8,"y")
Натискане Въведете ключ за получаване на резултата.
Седмици разлика между две дати
За да получите разликата в седмиците между две дати в клетка A11 и B11, моля, използвайте формулата като тази
=DATEDIF(A11,B11,"d")/7
Натискане Въведете ключ за получаване на резултата.
Забележка:
1) Когато използвате формулата по-горе, за да получите разликата в седмиците, тя може да върне резултат във формат на дата, трябва да форматирате резултата в общ или числов, както ви е необходимо.
2) Когато използвате формулата по-горе, за да получите разликата в седмиците, тя може да се върне към десетично число, ако искате да получите цялото число на седмицата, можете да добавите функция ROUNDDOWN преди това, както е показано по-долу, за да получите целочислената разлика в седмиците:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Изчислете месеците игнорирайте годините и дните между две дати
Ако просто искате да изчислите разликата в месеците, като игнорирате годините и дните между две дати, както показва екранната снимка по-долу, ето формула, която може да ви помогне.
=DATEDIF(A2,B2,"ym")
Натискане Въведете ключ за получаване на резултата.
A2 е началната дата, а B2 е крайната дата.
1.13 Изчисляване на дни игнориране на години и месеци между две дати
Ако просто искате да изчислите разликата в дните, като игнорирате години и месеци между две дати, както е показано на екранната снимка по-долу, ето формула, която може да ви помогне.
=DATEDIF(A5,B5,"md")
Натискане Въведете ключ за получаване на резултата.
A5 е началната дата, а B5 е крайната дата.
1.14 Изчислете разликата между две дати и върнете години, месеци и дни
Ако искате да получите разликата между две дати и да върнете xx години, xx месеца и xx дни, както показва екранната снимка по-долу, тук е предоставена и формула.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Натискане Въведете ключ за получаване на резултата.
A8 е началната дата, а B8 е крайната дата.
1.15 Изчислете разликата между дата и днес
За да изчислите автоматично разликата между дата и днес, просто променете end_date в горните формули на TODAY(). Тук трябва да изчислите разликата в дните между минала дата и днес като пример.
=DATEDIF(A11,TODAY(),"d")
Натискане Въведете ключ за получаване на резултата.
Забележка: ако искате да изчислите разликата между бъдеща дата и днес, променете началната_дата на днес и вземете бъдещата дата като крайна_дата по следния начин:
=DATEDIF(TODAY(),A14,"d")
Забележете, че началната_дата трябва да е по-малка от крайната_дата във функцията DATEDIF, в противен случай тя ще се върне към #NUM! стойност на грешката.
1.16 Изчислете работни дни със или без празник между две дати
Понякога може да се наложи да преброите броя на работните дни със или без празниците между две дадени дати.
В тази част ще използвате функцията NETWORKDAYS.INTL:
Кликнете NETWORKDAYS.INTL да знаете неговите аргументи и употреба.
Пребройте работните дни с празниците
За да преброите работните дни с празниците между две дати в клетка A2 и B2, моля, използвайте следната формула:
=NETWORKDAYS.INTL(A2,B2)
Натискане Въведете ключ за получаване на резултата.
Пребройте работните дни без празници
За да преброите работните дни с празници между две дати в клетка A2 и B2 и с изключение на празниците в диапазон D5:D9, моля, използвайте следната формула:
=NETWORKDAYS.INTL(A5;B5,1;5;D9:DXNUMX)
Натискане Въведете ключ за получаване на резултата.
Забележка:
В горните формули те приемат събота и неделя като уикенд, ако имате различни дни от почивните дни, моля, променете аргумента [уикенд] според нуждите ви.
1.17 Изчислете уикендите между две дати
Ако искате да преброите броя на почивните дни между две дати, функциите SUMPRODUCT или SUM могат да ви направят услуга.
За да преброите почивните дни (събота и неделя) между две дати в клетка A12 и B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Натискане Въведете ключ за получаване на резултата.
1.18 Изчислете конкретен ден от седмицата между две дати
За да преброите броя на конкретен ден от седмицата като понеделник между две дати, комбинацията от функциите INT и WEEKDAY може да ви помогне.
Клетка A15 и B15 са двете дати, между които искате да броите понеделник, моля, използвайте следната формула:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Натискане Въведете ключ за получаване на резултата.
Променете номера на деня от седмицата във функцията WEEKDAY, за да отчитате различен ден от седмицата:
1 е неделя, 2 е понеделник, 3 е вторник, 4 е сряда, 5 е четвъртък, 6 е петък и 7 е събота)
1.19 Изчислете оставащите дни в месеца/годината
Понякога може да искате да знаете оставащите дни от месеца или годината въз основа на предоставената дата, както показва екранната снимка по-долу:
Вземете оставащите дни в текущия месец
Кликнете EOMESEC да знаете аргумента и употребата.
За да получите оставащите дни от текущия месец в клетка A2, моля, използвайте следната формула:
=EOMONTH(A2,0)-A2
Натискане Въведете и плъзнете манипулатора за автоматично попълване, за да приложите тази формула към други клетки, ако е необходимо.
Съвет: резултатите могат да бъдат показани като формат на датата, просто ги променете като общ или числов формат.
Вземете оставащите дни в текущата година
За да получите оставащите дни от текущата година в клетка A2, моля, използвайте следната формула:
=DATE(YEAR(A2),12,31)-A2
Натискане Въведете и плъзнете манипулатора за автоматично попълване, за да приложите тази формула към други клетки, ако е необходимо.
1.21 Изчислете разликата между две времена
За да получите разликата между две времена, ето две прости формули, които могат да ви помогнат.
Да предположим, че в клетка A2 и B2 съдържат начално_време и крайно_време отделно, използвайки формулите като тези:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Натискане Въведете ключ за получаване на резултата.
Забележка:
- Ако използвате end_time-start_time, можете да форматирате резултата в друг формат за време, както ви е необходимо в диалоговия прозорец Форматиране на клетки.
- Ако използвате TEXT(end_time-first_time,"time_format"), въведете формата на часа, в който искате резултатът да се показва във формулата, като например TEXT(end_time-first_time,"h") връща 16.
- Ако крайното_време е по-малко от началното_време, и двете формули връщат стойности за грешка. За да разрешите този проблем, можете да добавите ABS в началото на тези формули, като ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), след което форматирайте резултата като време.
1.22 Изчислете разликата между две времена в часове/минути/секунди
Ако искате да изчислите разликата между два пъти в часове, минути или секунди, както показва екранната снимка по-долу, моля, следвайте тази част.
Получете часове разлика между две времена
За да получите часовата разлика между две времена в A5 и B5, моля, използвайте следната формула:
=INT((B5-A5)*24)
Натискане Въведете ключ, след което форматирайте резултата от формата на времето като общ или числов.
Ако искате да получите разликата в десетичните часове, използвайте (крайно_време-начално_време)*24.
Получете минути разлика между две времена
За да получите разликата в минутите между две времена в A8 и B8, моля, използвайте следната формула:
=INT((B8-A8)*1440)
Натискане Въведете ключ, след което форматирайте резултата от формата на времето като общ или числов.
Ако искате да получите разликата в десетичните минути, използвайте (крайно_време-начално_време)*1440.
Получете секунди разлика между две времена
За да получите разликата в секундите между две времена в A5 и B5, моля, използвайте следната формула:
=(B11-A11)*86400)
Натискане Въведете ключ, след което форматирайте резултата от формата на времето като общ или числов.
1.23 Изчислете разликата в часовете само между два пъти (не повече от 24 часа)
Ако разликата между две времена не надвишава 24 часа, функцията HOUR може бързо да получи разликата в часовете между тези две времена.
Кликнете HOUR за повече подробности относно тази функция.
За да получите разликата в часовете между времената в клетка A14 и B14, моля, използвайте функцията HOUR, както следва:
=HOUR(B14-A14)
Натискане Въведете ключ за получаване на резултата.
Началното_време трябва да е по-малко от крайното_време, в противен случай формулата връща #NUM! стойност на грешката.
1.24 Изчислете разликата в минути само между две времена (не повече от 60 минути)
Функцията MINUTE може бързо да получи разликата в минути между тези две времена и да игнорира часовете и секундите.
Кликнете MINUTE за повече подробности относно тази функция.
За да получите само разликата в минутите между времената в клетка A17 и B17, моля, използвайте функцията MINUTE, както следва:
=MINUTE(B17-A17)
Натискане Въведете ключ за получаване на резултата.
Началното_време трябва да е по-малко от крайното_време, в противен случай формулата връща #NUM! стойност на грешката.
1.25 Изчисляване на разликата в секунди само между две времена (не повече от 60 секунди)
Функцията SECOND може бързо да получи единствената разлика в секунди между тези две времена и да игнорира часовете и минутите.
Кликнете ВТОРИ за повече подробности относно тази функция.
За да получите само разликата в секунди между времената в клетка A20 и B20, моля, използвайте функцията SECOND по следния начин:
=SECOND(B20-A20)
Натискане Въведете ключ за получаване на резултата.
Началното_време трябва да е по-малко от крайното_време, в противен случай формулата връща #NUM! стойност на грешката.
1.26 Изчислете разликата между две времена и върнете часове, минути, секунди
Ако искате да покажете разликата между две времена като xx часа xx минути xx секунди, моля, използвайте функцията TEXT, както е показано по-долу:
Кликнете ТЕКСТ да осъзнае аргументите и използването на тази функция.
За да изчислите разликата между времената в клетка A23 и B23, използвайте следната формула:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Натискане Въведете ключ за получаване на резултата.
Забележка:
Тази формула също изчислява само разликата в часовете, която не надвишава 24 часа, а крайният_час трябва да е по-голям от началния_час, в противен случай тя връща #VALUE! стойност на грешката.
1.27 Изчислете разликата между две дати и часове
Ако има две времена във формат mm/dd/yyyy hh:mm:ss, за да изчислите разликата между тях, можете да използвате една от формулите по-долу, както ви е необходимо.
Получаване на часовата разлика между две дати и връщане на резултата във формат чч:мм:сс
Вземете две дати и часове в клетка A2 и B2 като пример, моля, използвайте формулата по следния начин:
=B2-A2
Натискане Въведете ключ, връщащ резултат във формат за дата и час, след което форматирайте този резултат като [h]:mm:ss в персонализираната категория под Телефон за връзка: раздела в Форматиране на клетки диалогов прозорец.
Получаване на разлика между две дати и време и връщане на дни, часове, минути, секунди
Вземете две дати и часове в клетка A5 и B5 като пример, моля, използвайте формулата по следния начин:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Натискане Въведете ключ за получаване на резултата.
Забележка: и в двете формули end_datetime трябва да е по-голямо от start_datetime, в противен случай формулите връщат стойности за грешка.
1.28 Изчислете разликата във времето с милисекунди
Първо, трябва да знаете как да форматирате клетката, за да покажете милисекундите:
Изберете клетките, които искате да показвате милисекунди, и изберете надясно Форматиране на клетки за да активирате Форматиране на клетки диалогов прозорец, изберете Custom в категория списък в раздела Номер и въведете това чч:мм:сс.000 в текстовото поле.
Използвайте формула:
Тук, за да изчислите разликата между две времена в клетка A8 и B8, използвайте формулата като:
=ABS(B8-A8)
Натискане Въведете ключ за получаване на резултата.
1.29 Изчисляване на работното време между две дати без почивните дни
Понякога може да се наложи да броите работните часове между две дати, с изключение на почивните дни (събота и неделя).
Тук работното време е фиксирано на 8 часа всеки ден и за да изчислите работното време между две дати, посочени в клетка A16 и B16, моля, използвайте следната формула:
=NETWORKDAYS(A16,B16) * 8
Натискане Въведете ключ и след това форматирайте резултата като общ или числов.
За повече примери за изчисляване на работното време между две дати, моля, посетете Вземете работни часове между две дати в Excel
Ако имате Kutools за Excel инсталиран в Excel, 90 процента от изчисленията на разликата в датата и часа могат да бъдат бързо решени, без да се запомнят никакви формули.
1.31 Изчислете разликата между две дати и часове от Data & Time Helper
За да изчислите разликата между две дати и часове в Excel, просто Помощник за дата и час е достатъчно.
1. Изберете клетка, където да поставите изчисления резултат, и щракнете Kutools > Помощник за формула > Помощник за дата и час.
2. В пукането Помощник за дата и час следвайте настройките по-долу:
- Проверка Разлика опция;
- Изберете начална дата и час в Въвеждане на аргументи можете също директно да въведете дата и час ръчно в полето за въвеждане или да щракнете върху иконата на календар, за да изберете датата;
- Изберете типа изходен резултат от падащия списък;
- Визуализирайте резултата в Резултат раздел.
3. кликване Ok. Изчисленият резултат се извежда и плъзнете манипулатора за автоматично попълване върху клетките, които също трябва да изчислите.
Съвет:
Ако искате да получите разликата между две дати и време и да покажете резултата като дни, часове и минути с Kutools за Excel, моля, направете следното:
Изберете клетка, в която искате да поставите резултата, и щракнете Kutools > Помощник за формула > Време за среща > Бройте дни, часове и минути между две дати.
След това в Помощник за формули диалоговия прозорец, посочете началната и крайната дата, след което щракнете Ok.
И резултатът от разликата ще бъде показан като дни, часове и минути.
Кликнете Помощник за дата и час за да научите повече за използването на тази функция.
Кликнете Kutools за Excel за да знаете всички функции на тази добавка.
Кликнете Безплатно сваляне за да получите 30-дневен безплатен пробен период на Kutools за Excel
Ако искате бързо да преброите уикенда, работните дни или конкретен делничен ден между две дати и часове, Kutools за Excel Помощник за формула група може да ви помогне.
1. Изберете клетката, която ще постави изчисления резултат, щракнете Kutools > Статистически > Брой неработни дни между две дати/Брой работни дни между две дати/Пребройте броя на конкретен ден от седмицата.
2. В изскачането Помощник за формули диалоговия прозорец, посочете начална дата и крайна дата, ако кандидатствате Пребройте броя на конкретен ден от седмицата, трябва да посочите и деня от седмицата.
За да преброите конкретния ден от седмицата, можете да се обърнете към бележката, за да използвате 1-7, за да посочите неделя-събота.
3. кликване Okи след това плъзнете манипулатора за автоматично попълване върху клетки, които трябва да преброят броя на уикенда/работния ден/конкретния делничен ден, ако е необходимо.
Кликнете Kutools за Excel за да знаете всички функции на тази добавка.
Кликнете Безплатно сваляне за да получите 30-дневен безплатен пробен период на Kutools за Excel
2. Добавете или извадете дата и час
Освен за изчисляване на разликата между две дати и часове, добавянето или изваждането също е нормалното изчисление на дата и час в Excel. Например, може да искате да получите крайната дата въз основа на датата на производство и броя на дните за съхранение на даден продукт.
2.11 Добавяне или изваждане на дни към дата
За да добавите или извадите определен брой дни към дадена дата, ето два различни метода.
Да предположим, че добавих 21 дни към дата в клетка A2, моля, изберете един от методите по-долу, за да го разрешите,
Метод 1 дата+дни
Изберете клетка и въведете формулата:
=A+21
Натискане Въведете ключ за получаване на резултата.
Ако искате да извадите 21 дни, знакът плюс (+) се променя на знак минус (-).
Метод 2 Специално поставяне
1. Въведете броя на дните, които искате да добавите, в клетка, като предположим в клетка C2, и след това натиснете Ctrl + C да го копирате.
2. След това изберете датите, на които искате да добавите 21 дни, щракнете с десния бутон, за да се покаже контекстното меню и изберете Специално поставяне....
3. В Специално поставяне диалог, проверка Добави опция (Ако искате да извадите дни, проверете изваждам опция). Кликнете OK.
4. Сега оригиналните дати се променят на 5-цифрени числа, форматирайте ги като дати.
2.12 Добавяне или изваждане на месеци към дата
За добавяне или изваждане на месеци към дата може да се използва функцията EDATE.
Кликнете EDATE да проучи аргументите и употребата му.
Да предположим, че добавите 6 месеца към датата в клетка A2, използвайте следната формула:
=EDATE(A2,6)
Натискане Въведете ключ за получаване на резултата.
Ако искате да извадите 6 месеца от датата, променете 6 на -6.
2.13 Добавяне или изваждане на години към дата
За да добавите или извадите n години към дата, може да се използва формула, която комбинира функциите ДАТА, ГОДИНА, МЕСЕЦ и ДЕН.
Да предположим, че добавите 3 години към датата в клетка A2, използвайте формулата, както следва:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Натискане Въведете ключ за получаване на резултата.
Ако искате да извадите 3 години от датата, променете 3 на -3.
2.14 Добавяне или изваждане на седмици към дата
За да добавите или извадите седмици към дата, общата формула е
Да предположим, че добавите 4 седмици към датата в клетка A2, използвайте формулата, както следва:
=A2+4*7
Натискане Въведете ключ за получаване на резултата.
Ако искате да извадите 4 седмици от датата, сменете знака плюс (+) на знак минус (-).
2.15 Добавете или извадете работните дни, включително или без празниците
В този раздел се въвежда как да използвате функцията WORKDAY за добавяне или изваждане на работни дни към дадена дата, без празници или включително празници.
посещение ДЕЛНИК за да научите повече подробности за неговите аргументи и употреба.
Добавете работни дни, включително празници
В клетка A2 е датата, която използвате, в клетка B2 съдържа броя на дните, които искате да добавите, моля, използвайте формулата, както следва:
=WORKDAY(A2,B2)
Натискане Въведете ключ за получаване на резултата.
Добавете работни дни без празниците
В клетка A5 е датата, която използвате, в клетка B5 съдържа броя на дните, които искате да добавите, в диапазон D5:D8 изброява празниците, моля, използвайте формулата, както следва:
=WORKDAY(A5,B5,D5:D8)
Натискане Въведете ключ за получаване на резултата.
Забележка:
Функцията WORKDAY приема събота и неделя като почивни дни, ако вашите почивни дни са събота и неделя, можете да приложите функцията WOKRDAY.INTL, която поддържа посочване на почивни дни.
посещение ДЕЛЕН ДЕН.ИНТ за повече информация.
Ако искате да извадите работни дни от дата, просто променете броя на дните на отрицателен във формулата.
2.16 Добавете или извадете конкретна година, месец, дни към дата
Ако искате да добавите конкретна година, месец дни към дата, формулата, която комбинира функцията ДАТА, ГОДИНА, МЕСЕЦ и ДНИ, може да ви направи услуга.
За да добавите 1 година, 2 месеца и 30 дни към дата в A11, моля, използвайте следната формула:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Натискане Въведете ключ за получаване на резултата.
Ако искате да извадите, сменете всички знаци плюс (+) на знаци минус (-).
2.21 Добавяне или изваждане на часове/минути/секунди към дата и час
Тук са дадени някои формули за добавяне или изваждане на часове, минути или секунди към дата и час.
Добавете или извадете часове към дата и час
Да предположим, че добавяме 3 часа към дата и час (също може да бъде време) в клетка A2, моля, използвайте формулата, както следва:
=A2+3/24
Натискане Въведете ключ за получаване на резултата.
Добавете или извадете часове към дата и час
Да предположим, че добавяме 15 минути към дата и час (също може да бъде време) в клетка A5, моля, използвайте формулата, както следва:
=A2+15/1440
Натискане Въведете ключ за получаване на резултата.
Добавете или извадете часове към дата и час
Да предположим, че добавяме 20 секунди към дата и час (също може да бъде време) в клетка A8, моля, използвайте формулата, както следва:
=A2+20/86400
Натискане Въведете ключ за получаване на резултата.
Да предположим, че има таблица в Excel, записваща работното време на всички служители за една седмица, за да сумирате общото работно време за изчисляване на плащанията, можете да използвате SUM(диапазон) за да получите резултата. Но като цяло сумираният резултат ще бъде показан като време, което не надвишава 24 часа, както показва екранната снимка по-долу, как можете да получите правилния резултат?
Всъщност просто трябва да форматирате резултата като [чч]:мм:сс.
Щракнете с десния бутон върху клетката с резултати, изберете Форматиране на клетки в контекстното меню и в изскачащото Форматиране на клетки диалогов прозорец, изберете Custom от списъка и въведете [чч]:мм:сс в текстовото поле в дясната секция щракнете 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))
Натискане Въведете ключ за получаване на резултата.
Ако имате инсталиран Kutools за Excel, само един инструмент – Дата и час Helper може да реши повечето изчисления при добавяне и изваждане на дата и час.
1. Щракнете върху клетка, която искате да изведе резултата, и приложете този инструмент, като щракнете Kutools > Помощник за формула > Помощник за дата и час.
2. В Помощник за дата и час диалог, проверка Добави опция или изваждам опция, както ви е необходима, след това изберете клетката или директно въведете датата и часа, в която искате да използвате Въвеждане на аргументи раздел, след което посочете годините, месеците, седмиците, дните, часовете, минутите и секундите, които искате да добавите или извадите, след което щракнете върху Ok. Вижте екранна снимка:
Можете да прегледате изчисления резултат в Резултат раздел.
Сега резултатът е изведен, плъзнете автоматичен манипулатор върху други клетки, за да получите резултатите.
Кликнете Помощник за дата и час за да научите повече за използването на тази функция.
Кликнете Kutools за Excel за да знаете всички функции на тази добавка.
Кликнете Такса за изтегляне за да получите 30-дневен безплатен пробен период на Kutools за Excel
2.41 Поставете отметка или маркирайте, ако дадена дата е изтекла
Ако има списък с изтекли дати на продукти, може да искате да проверите и маркирате датите, които са изтекли въз основа на днешния ден, както е показано на екранната снимка по-долу.
Всъщност Условно форматиране може бързо да се справи с тази работа.
1. Изберете датите, които искате да проверите, след което щракнете Начало > Условно форматиране > Ново правило.
2. В Ново правило за форматиране диалогов прозорец, изберете Използвайте формула, за да определите кои клетки да форматирате в Изберете тип правило раздел и тип = B2 в полето за въвеждане (B2 е първата дата, която искате да проверите) и щракнете формат да изскочи Форматиране на клетки диалогов прозорец, след което изберете различно форматиране, за да изпреварите изтеклите дати, както ви е необходимо. Кликнете OK > OK.
2.42 Връща края на текущия месец/първия ден на следващия месец/a>
Изтеклите дати на някои продукти са в края на месеца на производство или първия ден от следващия месец на производство, за бърз списък на изтеклите дати въз основа на датата на производство, моля, следвайте тази част.
Вземете края на текущия месец
Ето дата на производство в клетка B13, моля, използвайте формулата по следния начин:
=EOMONTH(B13,0)
Натискане Въведете ключ за получаване на резултата.
Вземете 1-ви ден от следващия месец
Ето дата на производство в клетка B18, моля, използвайте формулата по следния начин:
=EOMONTH(B18,0)+1
Натискане Въведете ключ за получаване на резултата.
3. Изчислете възрастта
В този раздел са изброени методите за решаване на това как да се изчисли възрастта въз основа на дадена дата или сериен номер.
3.11 Изчислете възрастта въз основа на дадена дата на раждане
Вземете възрастта в десетично число въз основа на датата на раждане
Кликнете YEARFRAC за подробности относно неговите аргументи и употреба.
Например, за да получите възрастта въз основа на списъка с рождени дати в колона B2:B9, моля, използвайте следната формула:
=YEARFRAC(B2,TODAY())
Натискане Въведете клавиш, след което плъзнете манипулатора за автоматично попълване надолу, докато се изчислят всички възрасти.
Съвет:
1) Можете да посочите десетичния знак, както ви е необходим в Форматиране на клетки диалогов прозорец.
2) Ако искате да изчислите възрастта на конкретна дата въз основа на дадена дата на раждане, променете TODAY() на конкретната дата, оградена с двойни кавички, като =YEARFRAC(B2,"1/1/2021")
3) Ако искате да получите възрастта за следващата година въз основа на датата на раждане, просто добавете 1 във формулата като =YEARFRAC(B2,TODAY())+1.
Вземете възрастта в цяло число въз основа на датата на раждане
Кликнете DATEDIF за подробности относно неговите аргументи и употреба.
Използвайки горния пример, за да получите възрастта въз основа на рождените дати в списъка в B2:B9, моля, използвайте следната формула:
=DATEDIF(B2,TODAY(),"y")
Натискане Въведете клавиш, след което плъзнете манипулатора за автоматично попълване надолу, докато се изчислят всички възрасти.
Съвет:
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"
Натискане Въведете за да получите възрастта, след което плъзнете манипулатора за автоматично попълване надолу към други клетки.
Съвет:
Ако искате да изчислите възрастта на конкретна дата въз основа на дадена рождена дата, променете 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. Натиснете Друг + F11 ключове за активиране Microsoft Visual Basic за приложения прозорец и щракнете Поставете и изберете Модули за създаване на нов модул.
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(рождена дата,смъртна дата), в този случай, =AgeFunc(B22,C22), натиснете клавиша Enter, за да получите възрастта. И използвайте манипулатора за автоматично попълване, за да приложите тази формула към други клетки, ако е необходимо.
Ако имате Kutools за Excel инсталиран в Excel, можете да приложите Помощник за дата и час инструмент за изчисляване на възрастта.
1. Изберете клетка, в която искате да поставите изчислената възраст, и щракнете Kutools > Помощник за формула > Помощник за дата и час.
2. В Помощник за дата и час диалогов прозорец,
- 1) Проверете възраст опция;
- 2) Изберете клетката за рождена дата или директно въведете рождената дата или щракнете върху иконата на календар, за да изберете рождената дата;
- 3) Изберете днес опция, ако искате да изчислите текущата възраст, изберете Посочена дата опция и въведете датата, ако искате да изчислите възрастта в миналото или бъдещето;
- 4) Посочете вида на изхода от падащия списък;
- 5) Визуализирайте изходния резултат. Кликнете Ok.
Кликнете Помощник за дата и час за да научите повече за използването на тази функция.
Кликнете Kutools за Excel за да знаете всички функции на тази добавка.
Кликнете Безплатно сваляне за да получите 30-дневен безплатен пробен период на Kutools за Excel
3.31 Получаване на рожден ден от ID номер
Ако има списък с идентификационни номера, които използват първите 6 цифри за запис на рождената дата, като например 920315330 означава, че рождената дата е 03/15/1992, как можете бързо да поставите рождената дата в друга колона?
Сега нека вземем за пример списъка с идентификационни номера, започващи в клетка C2, и използваме формулата по следния начин:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Натискане Въведете ключ. След това плъзнете манипулатора за автоматично попълване надолу, за да получите други резултати.
Забележка:
Във формулата можете да промените препратката към вашите нужди. Например, ако идентификационният номер, показан като 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")
Натискане Въведете ключ. След това плъзнете манипулатора за автоматично попълване надолу, за да получите други резултати.
Забележка:
В тази формула, ако годината е по-малка от текущата година, годината ще се счита за започваща с 20, като например 200203943 ще се счита за година 2020; ако годината е по-голяма от текущата година, годината ще се счита за започваща с 19, като например 920420392 ще се счита за година 1992.
Още уроци за Excel:
Комбинирайте няколко работни книги/работни листове в една
Този урок, изброяващ почти всички комбинирани сценарии, с които може да се сблъскате, и предоставя относително професионални решения за вас.
Разделяне на клетки с текст, номера и дата (разделяне на няколко колони)
Този урок е разделен на три части: разделени текстови клетки, разделени клетки с числа и разделени клетки с дата. Всяка част предоставя различни примери, за да ви помогне да знаете как да се справите със задачата за разделяне, когато се натъкнете на същия проблем.
Комбинирайте съдържанието на множество клетки, без да губите данни в Excel
Този урок стеснява извличането до конкретна позиция в клетка и събира различни методи за подпомагане на извличането на текст или числа от клетка по конкретна позиция в Excel.
Сравнете две колони за съвпадения и разлики в Excel
Тук тази статия обхваща повечето възможни сценарии за сравняване на две колони, които може да срещнете, и се надяваме, че може да ви помогне.
Най-добрите инструменти за производителност в офиса
Kutools за Excel решава повечето от вашите проблеми и увеличава вашата производителност с 80%
- Супер Формула Бар (лесно редактиране на няколко реда текст и формула); Оформление за четене (лесно четене и редактиране на голям брой клетки); Поставяне във филтриран диапазон...
- Обединяване на клетки/редове/колони и съхраняване на данни; Съдържание на разделени клетки; Комбинирайте дублиращи се редове и сума/средно... Предотвратяване на дублиращи се клетки; Сравнете диапазони...
- Изберете Дублиран или Уникален редове; Изберете Празни редове (всички клетки са празни); Super Find и Fuzzy Find в много работни тетрадки; Произволен избор...
- Точно копие Множество клетки без промяна на референтната формула; Автоматично създаване на препратки към множество листа; Вмъкване на куршуми, квадратчета за отметка и други...
- Любими и бързо вмъкнати формули, диапазони, диаграми и снимки; Шифроване на клетки с парола; Създаване на пощенски списък и изпращайте имейли...
- Извличане на текст, Добавяне на текст, Премахване по позиция, Премахване на пространството; Създаване и отпечатване на междинни суми за пейджинг; Конвертиране на съдържание и коментари между клетки...
- Супер филтър (запазване и прилагане на филтърни схеми към други листове); Разширено сортиране по месец/седмица/ден, честота и други; Специален филтър с удебелен шрифт, курсив...
- Комбинирайте работни тетрадки и работни листове; Обединяване на таблици въз основа на ключови колони; Разделете данните на няколко листа; Пакетно конвертиране на xls, xlsx и PDF...
- Групиране на обобщена таблица по номер на седмицата, ден от седмицата и други... Показване на отключени, заключени клетки с различни цветове; Маркирайте клетки, които имат формула/име...
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
