Изчислете добавяне и изваждане на време (времева разлика, работно време) в Excel

порадиДатите и часовете се съхраняват като числа в бекенда на Excel, така че можете лесно да използвате прости аритметични операции и формули върху стойностите за дата и час.

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

В този урок ще ви покажа как да Извършвайте изчисления с помощта на време в Excelняколко метода (като изчисляване на часовата разлика, добавяне и изваждане на време, показване на времето в различни формати и изчисляване на сумата от времеви стойности).

Как Excel обработва датите и часовете?

Както споменах, датите и часовете се съхраняват като числа в клетки в Excel.Цело число представлява целия ден, дробна част от числото представлява частта от деня (може да се преобразува в стойности за часове, минути и секунди)

Например, стойност от 1 представлява 1900 януари 1 г. в Excel, което е отправната точка за Excel да започне да разглежда датите.

Така че 2 означава 1990 януари 1 г., 2 означава 3 януари 1900 г. и така нататък, 1 означава 3 януари 44197 г.

Забележка: Excel за Windows и Excel за Mac следват различни начални дати.1 в Excel за Windows означава 1900 януари 1 г., 1 в Excel за Mac означава 1 януари 1904 г.

Ако тези числа имат цифри след десетичната запетая, Excel третира тези числа като част от ден и може да ги преобразува в часове, минути и секунди.

Например 44197.5 означава 2021 януари 1 г. в 1:12:00.

Така че, ако работите със стойности за време в Excel, вие основно работите с дробната част от числото.

Excel ви дава гъвкавостта да конвертирате дробни части в различни формати, като само часове, само минути, само секунди или комбинация от часове, минути и секунди

Сега, когато разбирате как времето се съхранява в Excel, нека разгледаме някои примери за това как да изчислим разликата във времето между две различни дати или часове в Excel

Формулата за изчисляване на разликата във времето между две времена

В много случаи всичко, което трябва да направите, е да разберете общото изминало време между две времеви стойности (например в случай на график с In-time и Out-time).

Избраният от вас метод зависи от това как е посочено времето в клетката и как искате да бъде форматиран резултатът.

Нека разгледаме няколко примера

Просто изваждане за изчисляване на разликата във времето в Excel

Тъй като времената се съхраняват като числа в Excel, за да намерите разликата между 2 времеви стойности, можете лесно да извадите началния час от крайния.

крайно време - начален час

Резултатът от изваждането също ще бъде десетична стойност, представляваща изминалото време между двете времеви стойности.

По-долу е пример, в който имам начално и крайно време и съм изчислил часовата разлика чрез просто изваждане.

Възможно е резултатите ви да се показват във времеви формат (а не в десетични или часови/минутни стойности).В примера по-горе резултатът в клетка C2 показва 09:30 вместо 9.5.

Това е добре, защото Excel ще се опита да копира форматирането от съседни колони.

За да го преобразувате в десетичен знак, променете формата на клетката на "Общ" (тази опция е в раздела "Начало" на групата "Числа")

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

Ето различните формати, които можете да използвате:

формат	какво може да направи
hh	Показва само броя часове, изминали между две дати
mm	Показване на часовете като две цифри (напр. 04 или 12)
чч: мм	Показва часове и минути, изминали между две дати, напр. 10:20
чч: мм: сс	Показва часове, минути и секунди, изминали между две дати, напр. 10:20:36

Ако се чудите къде и как да приложите тези персонализирани формати за дата, следвайте тези стъпки:

1. Изберете клетките, към които да приложите формата на датата
2. Задръжте Control и натиснете 1 (Command + 1, ако използвате Mac)
3. В диалоговия прозорец Форматиране на клетки, който се отваря, щракнете върху раздела Числа (ако вече не е избран)
4. В левия прозорец щракнете върху Персонализиране
5. Въведете произволен код за формат в полето Тип (в този пример използвам hh:mm:ss)
6. Щракнете върху OK

Горните стъпки ще променят формата и ще ви покажат стойността според формата.

Имайте предвид, че персонализираното форматиране на числата не променя стойността в клетката.Просто променя начина, по който се показва стойността.Така че мога да избера да показвам само стойността на часа в клетката и тя все още има оригиналната стойност.

ПРОФЕСИОНАЛЕН СЪВЕТ: Ако общият брой часове надвишава 24 часа, вместо това използвайте следния персонализиран цифров формат: [hh]:mm:ss

Изчислете разликата във времето в часове, минути или секунди

Когато изваждате стойности за време, Excel връща десетично число, което представлява получената времева разлика.

Тъй като всяко цяло число представлява ден, дробната част от числото ще представлява по-голямата част от деня и може лесно да се преобразува в часове, минути или секунди.

Изчислете часовата разлика в часове

Да предположим, че имате набор от данни, както е показано по-долу, и искате да изчислите броя на часовете между две времеви стойности

Формулата по-долу ще ви даде часовата разлика в часовете:

=(B2-A2)*24

Горната формула ще ви даде общия брой часове, изминали между две времеви стойности.

Понякога Excel ще се опита да помогне и също така ще ви даде резултатите във времеви формат (показан по-долу).

Можете лесно да го преобразувате в числов формат, като щракнете върху раздела Начало и изберете Число като формат в групата Число.

Ако просто искате да върнете общия брой часове, изминали между два пъти (без дробна част), използвайте тази формула:

=INT((B2-A2)*24)

Забележка: Тази формула работи само ако двете времеви стойности са в един и същи ден.Тази формула ще даде грешни резултати, ако датите се променят (една от стойностите на времето е друга дата, а втората е друга дата).Когато датата се промени по-късно в този урок, вижте раздела, където представям формулата за изчисляване на часовата разлика.

Изчислете часовата разлика в минути

За да изчислите часовата разлика в минути, трябва да умножите получената стойност по общия брой минути на ден (т.е. 1440 или 24*60).

Да предположим, че имате набор от данни, както е показано по-долу, и искате да изчислите общия брой минути, изминали между началната и крайната дата.

Ето формулата, която може да направи това:

=(B2-A2)*24*60

Изчислете часовата разлика в секунди

За да изчислите часовата разлика в секунди, трябва да умножите получената стойност по общия брой секунди на ден (т.е. 24*60*60 или 86400).

Да предположим, че имате набор от данни като по-долу и искате да преброите общия брой секунди, които са изминали между началната и крайната дата.

Ето формулата, която може да направи това:

=(B2-A2)*24*60*60

Изчислете часовата разлика с помощта на функцията ТЕКСТ

Друг лесен начин бързо да получите разликата във времето, без да се притеснявате за промяна на формата, е да използвате функцията TEXT.

Функцията TEXT ви позволява да зададете форматиране във формули.

=TEXT(крайна_дата-начална_дата, формат)

Първият параметър е изчислението, което искате да извършите, а вторият параметър е форматът, в който искате да покажете резултатите от изчислението.

Да предположим, че имате набор от данни, както е показано по-долу, и искате да изчислите разликата във времето между две пъти.

Ето някои формули, които ще ви дадат резултатите в различни формати

Показване само на часовете:

=TEXT(B2-A2,"hh")

Горната формула ще ви даде резултат само от броя часове, изминали между две времеви стойности.Ако резултатът ви е 9 часа 30 минути, той пак ще показва само 9.

Показване на общия брой минути

=TEXT(B2-A2,"[mm]")

Показване на общия брой секунди

=ТЕКСТ(B2-A2,"[ss]")

Показва часове и минути

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

Показва часове, минути и секунди

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

Ако се чудите каква е разликата между hh и [hh] във формата (или mm и [mm]), когато използвате квадратни скоби, това ви дава общия брой часове между двете дати, дори ако часът стойности повече от 24.Така че, ако изваждате две стойности за дата, които се различават с повече от 24 часа, използването на [hh] ще ви даде общия брой часове, докато hh ще ви даде само броя часове, изминали на крайната дата.

Вземете разликата във времето в една единица (часове/минути), пренебрегвайки други

Ако просто искате да изчислите разликата във времето между две времеви стойности в часове, минути или секунди, можете да използвате специалните функции ЧАС, МИНУТА или СЕКУНДА.

Всяка от тези функции приема един параметър, стойността на времето, и връща определената времева единица.

Да предположим, че имате набор от данни, както е показано по-долу, и искате да изчислите общия брой часове и секунди, които са изминали между тези две времена.

Ето формулата, за да направите това:

Изчислете броя на часовете, изминали между два пъти

=ЧАС(B2-A2)

Изчислете минутите въз основа на резултата от стойността на времето (с изключение на завършените часове)

=МИНУТА(B2-A2)

Изчислете секундите от резултата от времевата стойност (с изключение на завършените часове и минути)

=ВТОРА (B2-A2)

Няколко неща, които трябва да знаете, когато използвате тези формули за ЧАСове, МИНУТИ и ВТОРИ:

• Разликата между крайния и началния час не може да бъде отрицателна (това обикновено се случва при промяна на датата).В този случай тези формули ще върнат #NUM!грешка
• Тези формули използват само частта от времето (и игнорират частта от дните) от получената времева стойност.Така че, ако разликата между крайния и началния час е 2 дни, 10 часа, 32 минути и 44 секунди, формулата ЧАС ще даде 10, формулата МИНУТА ще даде 32, а формулата ВТОРА ще даде 44

Изчислете времето, изминало сега (от началния час)

Ако искате да изчислите общото изминало време между началния и текущия час, можете да използвате формулата СЕГА вместо крайния час.

Функцията NOW връща текущата дата и час в клетката, в която се използва.Това е една от функциите, която не приема никакви входни аргументи.

Така че, ако искате да изчислите общото изминало време между началното и текущото време, можете да използвате следната формула:

=СЕГА() - начален час

По-долу е даден пример, където имам началния час в колона A и времето до момента в колона B.

Ако часовата разлика между началната дата и час и текущото време е повече от 24 часа, можете да форматирате резултата, за да покажете частта за дата и час.

Можете да направите това със следната формула TEXT:

=TEXT(СЕГА()-A2,"dd hh:ss:mm")

Можете също да постигнете същото, като промените персонализирания формат на клетката (както е описано по-рано в този урок), така че да показва частта за дата и час.

Ако началният ви час има само частта за времето, Excel го третира като 1990 януари 1 г.

В този случай, ако използвате функцията СЕГА за изчисляване на изминалото време до момента, това ще ви даде грешен резултат (защото получената стойност ще съдържа и общия брой дни, изминали от 1990 януари 1 г.).

В този случай можете да използвате следната формула:

=СЕГА()-INT(СЕГА())-A2

Горната формула използва функцията INT, за да премахне частта от дните от стойността, върната от сега функцията, която след това се използва за изчисляване на разликата във времето.

Имайте предвид, че СЕГА е променлива функция, която се актуализира, когато листът се промени, но не в реално време

Изчислете времето при промяна на датата (изчислете и покажете отрицателно време в Excel)

Описаните досега методи работят добре, ако крайното ви време е по-късно от началното.

Проблемът обаче възниква, когато крайното ви време е по-малко от началното.Това обикновено се случва, когато попълвате график и въвеждате само часа, а не цялата дата и час.

В този случай, ако работите нощна смяна и датата се промени, крайният ви час може да е по-рано от началния ви час.

Например, ако започнете работа в 6:00 часа и приключите работата и направите пауза в 9:00 часа сутринта.

Ако сте използвали само стойности за време, тогава изваждането на началния час от крайното време ще даде отрицателна стойност от 9 часа (9 – 18).

Excel не може да се справи с отрицателни времеви стойности (и, по този въпрос, хората, освен ако не можете да пътувате във времето)

В този случай имате нужда от начин да определите, че датата се е променила, и трябва да изчислите съответно.

За щастие има много просто решение.

Да предположим, че имате набор от данни като по-долу, където имам начален и краен час.

Както ще забележите, понякога началният час е вечер, а крайният час е сутрин (показва, че е била смяна през нощта и денят се е променил).

Ако използвам формулата по-долу, за да изчисля часовата разлика, тя показва знак за паунд (маркиран в жълто на изображението по-долу) в клетките, където резултатът е отрицателна стойност.

=B2-A2

Това е IF формула, ако стойността на времевата разлика е отрицателна, ако е отрицателна, връща правилния резултат

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

Въпреки че това работи добре в повечето случаи, все още не е достатъчно, ако началното и крайното време са на повече от 24 часа.Например някой влиза в 1:9 ч. на ден 00 и излиза в 2:11 ч. на ден 00.

Тъй като това е повече от 24 часа, няма начин да се разбере дали лицето се отказва след 2 часа или 26 часа.

Въпреки че най-добрият начин да поправите това е да се уверите, че записът включва датата и часа, ако това е само времето, с което работите, тогава горната формула трябва да направи по-голямата част от проблема (като се има предвид, че е малко вероятно някой да работи повече от 24 часа)

Добавяне и изваждане на време в Excel

Досега сме виждали примери за начално и крайно време, трябва да намерим часовата разлика.

Excel също така ви позволява лесно да добавяте или изваждате фиксирани стойности за време от съществуващите стойности за дата и час.

Например, да предположим, че имате списък със задачи на опашка, където всяка задача отнема определено време и искате да знаете кога всяка задача приключва.

В този случай можете лесно да добавите времето, което ще отнеме всяка задача към началния час, за да видите кога се очаква задачата да бъде завършена.

Тъй като Excel съхранява стойностите за дата и час като числа, трябва да се уверите, че времето, което се опитвате да добавите, е във формат, който Excel вече следва.

Например, ако добавите 1 към дата в Excel, тя ще ви даде следващата дата.Това е така, защото 1 представлява цял ден (еквивалентен на 24 часа) в Excel.

Така че, ако искате да добавите 1 час към съществуваща времева стойност, не можете да продължите и просто да добавите 1 към нея.Трябва да се уверите, че сте преобразували стойността на часа в дробна част, представляваща час.Същото важи и за добавянето на минути и секунди.

Използвайте функцията за време

Функциите за време в Excel вземат стойности за час, минута и секунда и ги преобразуват в десетично число, представляващо времето.

Например, ако искам да добавя 4 часа към съществуващото време, мога да използвам следната формула:

= начален час + ВРЕМЕ(4,0,0)

Това е полезно, ако знаете часовете, минутите и секундите, които да добавите към съществуващо време, и можете просто да използвате функцията ВРЕМЕ, без да се притеснявате за правилното преобразуване на времето в десетична стойност.

Също така имайте предвид, че функцията ВРЕМЕ разглежда само целочислената част от стойностите на часовете, минутите и секундите, които въвеждате.Например, ако използвам 5.5 часа във функцията ВРЕМЕ, тя просто добавя 5 часа и игнорира дробната част.

Също така имайте предвид, че функцията TIME може да добавя само стойности, които са на по-малко от 24 часа.Това ще ви даде неправилен резултат, ако стойността на часовете ви надвиши 24.

Същото важи и за частите за минутите и секундите, функцията ще отчита само стойности по-малко от 60 минути и 60 секунди

Точно както използвах функцията ВРЕМЕ за добавяне на време, можете също да изваждате времето.Просто променете знака + в горната формула на знак минус

Използвайте основна аритметика

Докато функцията за време е лесна за използване, тя има някои ограничения (както е споменато по-горе).

Ако искате повече контрол, можете да използвате аритметичния метод, който ще разгледам тук.

Концепцията е проста - преобразувайте времева стойност в десетична стойност, представляваща частта от деня, която след това можете да добавите към всяка стойност на времето в Excel.

Например, ако искате да добавите 24 часа към съществуваща стойност на времето, можете да използвате следната формула:

= начален час + 24/24

Това просто означава, че добавям ден към съществуващата времева стойност.

Сега като вземете същата концепция, да кажем, че искате да добавите 30 часа към стойността на времето, можете да използвате следната формула:

= начален час + 30/24

Формулата по-горе прави същото, където цялата част от (30/24) представлява общия брой дни от времето, което искате да добавите, а дробната част представлява часове/минути/секунди

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

=Начален час +(Добавяне на минути)/24*60

Ако имате секунди за добавяне, можете да използвате тази формула:

= начален час + (допълнителни минути)/24*60*60

Въпреки че този метод не е толкова прост, колкото използването на функцията за време, аз го намирам за много по-добър, защото работи във всички случаи и следва едни и същи концепции.За разлика от функцията за време, не е нужно да се притеснявате за добавяне на време по-малко или по-голямо от 24 часа

Можете също да следвате същата концепция, докато изваждате времето.Просто променете + на отрицателен знак в горната формула

Как да сумирате времето в Excel

Понякога може да искате бързо да добавите всички времеви стойности в Excel.Добавянето на множество времеви стойности в Excel е лесно (само проста формула SUM)

Въпреки това, когато добавяте време в Excel, има няколко неща, които трябва да знаете, особено формата на клетката, който ще ви покаже резултатите.

Нека да разгледаме един пример.

По-долу имам списък със задачи в колона B и времето, което ще отнеме всяка задача, и искам бързо да добавя тези времена и да знам общото време, което ще отнеме всички тези задачи.

В клетка B9 използвах проста формула СУМ за изчисляване на общото време, което всички тези задачи ще отнеме и ми дава стойност от 18:30 (което означава, че ще отнеме 18 часа и 20 минути, за да изпълня всички тези задачи)

Дотук добре!

Как да сумирате над 24 часа в Excel

Сега вижте какво се случва, когато променя времето за изпълнение на задача 2 от 1 час на 10 часа.

Резултатът сега показва 03:20, което означава, че ще са необходими 3 часа и 20 минути, за да се изпълнят всички тези задачи.

Това е неправилно (очевидно)

Проблемът тук не е, че Excel е прецакан.Проблемът тук е, че клетките са форматирани да показват само времевата част от получената стойност.

И тъй като получената стойност тук надвишава 24 часа, Excel решава да преобразува 24-часовата част в ден, да я премахне от стойността, показана на потребителя, и да покаже само оставащите часове, минути и секунди.

За щастие това е лесно да се поправи.

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

Ето някои формати, които можете да използвате:

формат	очакван резултат
[h]: мм	28:30
[m]:ss	1710:00
d “D” чч:мм	Ден 04 30:XNUMXч
d "D" hh "Min" ss "Sec"	1 ден 04 минути 00 секунди
d “Ден” hh “Минута” ss “Секунди”	1 ден 04 минути 00 секунди

Можете да промените формата, като отидете в диалоговия прозорец Format Cells и приложите персонализиран формат, или използвате функцията TEXT и използвате някой от горните формати в самата формула

Можете да използвате следната формула TEXT, за да покажете времето, дори ако то надвишава 24 часа:

=TEXT(SUM(B2:B7),"[h]:mm:ss")

Или ако искате да преобразувате часове над 24 часа в дни, използвайте следната формула:

=TEXT(SUM(B2:B7),"dd hh:mm:ss")

Резултатите показват хеш (###) вместо дата/час (причина + поправка)

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

Ето някои възможни причини и как да ги коригирате:

Колоните не са достатъчно широки

Когато клетката няма достатъчно място за показване на пълната дата, тя може да покаже знак за лира.

Има лесна поправка - променете ширината на колоната и я направете по-широка.

отрицателна стойност на датата

Стойностите за дата или час в Excel не могат да бъдат отрицателни.Ако изчислявате разликата във времето и резултатът е отрицателен, Excel ще покаже хеш знака.

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

В други случаи можете да използвате функцията ABS за преобразуване на отрицателни времеви стойности в положителни числа за правилно показване.Като алтернатива можете да използвате формула IF, за да проверите дали резултатът е отрицателен и да върнете по-смислена стойност.

В този урок разгледах теми за изчисляване на времето в Excel (където можете да изчислявате разликите във времето, да добавяте и изваждате време, да показвате времето в различни формати и да сумирате стойности на времето)

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

Често задавани въпроси за изчисляване на събиране и изваждане на време в Excel