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

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

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

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

Как Excel се справя с датата и часа?

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

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

И така, 2 ще означава 02 януари 1990 г., 3 ще означава 03 януари 1900 г. и така нататък, а 44197 ще означава 01 януари 2021 г.

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

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

Например 44197,5 би означавало 01 януари 2021 г. 12:00:00 ч.

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

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

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

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

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

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

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

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

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

Краен час - Начален час

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

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

Има вероятност резултатите ви да се показват във формат на времето (вместо десетични знаци или в стойности на часове/минути). В горния ни пример резултатът в клетка C2 показва 09:30 сутринта вместо 9,5.

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

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

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

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

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

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

1. Изберете клетките, към които искате да приложите формата на датата
2. Задръжте клавиша Control и натиснете клавиша 1 (или Command + 1, ако използвате Mac)
3. В диалоговия прозорец Форматиране на клетки, който се отваря, щракнете върху раздела Число (ако вече не е избрано)
4. В левия прозорец щракнете върху Персонализиран
5. Въведете който и да е код на желания формат в полето Type (в този пример използвам 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 ви позволява да посочите формата точно във формулата.

= ТЕКСТ (Крайна дата - начална дата, формат)

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

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

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

Показвайте само броя на часовете:

= ТЕКСТ (B2-A2, "hh")

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

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

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

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

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

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

= ТЕКСТ (B2-A2, "[hh]: mm")

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

= ТЕКСТ (B2-A2, "hh: mm: ss")

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

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

Ако искате да изчислите времевата разлика между двете стойности във времето само в броя на часовете или минути или секунди, тогава можете да използвате специалната функция ЧАС, МИНУТА или ВТОРИ.

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

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

По -долу са формулите за това:

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

= ЧАС (B2-A2)

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

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

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

= ВТОРО (B2-A2)

Няколко неща, които трябва да знаете, когато работите с тези формули HOURS, MINUTE и SECOND:

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

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

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

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

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

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

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

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

Можете да направите това, като използвате формулата ТЕКСТ по -долу:

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

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

В случай, че началният ви час има само част от времето, Excel ще го приеме за час на 1 януари 1990 г.

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

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

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

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

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

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

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

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

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

Например, ако започнете работата си в 18:00 вечерта и завършите работата си и почивка в 9:00 сутринта.

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

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

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

За щастие, има наистина лесно решение за това.

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

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

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

= В2-А2

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

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

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

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

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

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

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

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

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

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

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

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

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

Използване на функцията ВРЕМЕ

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

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

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

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

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

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

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

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

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

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

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

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

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

= Start_time + 24/24

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

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

= Start_time + 30/24

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

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

= Start_time + (минути за добавяне)/24*60

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

= Start_time + (минути за добавяне)/24*60*60

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

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

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

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

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

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

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

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

Всичко добре дотук!

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

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

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

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

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

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

Това, за щастие, има лесно решение.

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

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

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

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

Можете да използвате формулата ТЕКСТ по -долу, за да покажете часа, дори когато е повече от 24 часа:

= ТЕКСТ (SUM (B2: B7), "[h]: mm: ss")

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

= ТЕКСТ (SUM (B2: B7), "dd hh: mm: ss")

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

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

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

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

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

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

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

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

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

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

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

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