Израчунајте време у Екцелу (временска разлика, одрађени сати, додавање/ одузимање)

Будући да су датуми и времена ускладиштени као бројеви на задњој страни програма Екцел, можете лако користити једноставне аритметичке операције и формуле за вредности датума и времена.

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

У овом водичу ћу вам показати неколико начина за обављање прорачуна користећи време у Екцелу (попут израчунавања временске разлике, додавања или одузимања времена, приказивања времена у различитим форматима и израчунавања збира временских вредности)

Како Екцел поступа са датумом и временом?

Као што сам споменуо, датуми и времена се чувају као бројеви у ћелији у Екцелу. Цијели број представља цијели дан, а децимални дио броја представља дио дана (који се може претворити у вриједности сати, минута и секунди)

На пример, вредност 1 представља 01. јануар 1900. у Екцелу, што је почетна тачка од које Екцел почиње да разматра датуме.

Дакле, 2 би значило 02. јануара 1990., 3 би значило 3. јануара 1900 и тако даље, а 44197 би значило 1. јануара 2021.

Напомена: Екцел за Виндовс и Екцел за Мац следе различите датуме почетка. 1 у програму Екцел за Виндовс значило би 1. јануара 1900. године, а 1 у програму Екцел за Мац значило би 1. јануара 1904. године

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

На пример, 44197,5 би значило 1. јануара 2021 12:00:00 ПМ.

Дакле, ако радите са временским вредностима у Екцелу, у основи бисте радили са децималним делом броја.

Екцел вам даје флексибилност да тај децимални део претворите у различите формате, као што су само сати, само минуте, само секунде или комбинација сати, минута и секунди

Сада када разумете како се време чува у Екцелу, погледајмо неке примере како израчунати временску разлику између два различита датума или времена у Екцелу

Формуле за израчунавање временске разлике између два времена

У многим случајевима, све што желите да урадите је да сазнате укупно време које је протекло између двоструких вредности (на пример у случају временског листа који има време и време истека).

Метод који одаберете зависиће од тога како је време наведено у ћелији и у ком формату желите резултат.

Погледајмо неколико примера

Једноставно одузимање израчунајте временску разлику у Екцелу

Пошто је време ускладиштено као број у Екцелу, пронађите разлику између 2 вредности времена, можете лако одузети време почетка од времена завршетка.

Време завршетка - Време почетка

Резултат одузимања такође би била децимална вредност која би представљала време које је протекло између две временске вредности.

Испод је пример где имам почетак и крај времена и израчунао сам временску разлику једноставним одузимањем.

Постоји могућност да се ваши резултати прикажу у формату времена (уместо децималних места или у вредностима сати/минута). У нашем горњем примеру, резултат у ћелији Ц2 приказује 09:30 уместо 9,5.

То је сасвим у реду јер Екцел покушава да копира формат из суседне колоне.

Да бисте ово претворили у децимални број, промените формат ћелија у Опште (опција је на картици Почетна у групи Бројеви)

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

Испод су различити формати које можете користити:

Формат	Шта ради
х	Приказује само сате протекле између два датума
ХХ	Приказује двоцифрене сате (на пример 04 или 12)
хх: мм	Приказује сате и минуте који су протекли између два датума, на пример 10:20
хх: мм: сс	Приказује сате, минуте и секунде које су протекле између два датума, на пример 10:20:36

А ако се питате где и како применити ове прилагођене формате датума, следите доле наведене кораке:

1. Изаберите ћелије на које желите да примените формат датума
2. Држите контролни тастер и притисните тастер 1 (или Цомманд + 1 ако користите Мац)
3. У дијалогу Форматирање ћелија који се отвори кликните на картицу Број (ако већ није изабрана)
4. У левом окну кликните на Прилагођено
5. Унесите било који од жељених формата у поље Типе (у овом примеру користим хх: мм: сс)
6. Притисните ОК

Горе наведени кораци би променили обликовање и приказали би вам вредност на основу формата.

Имајте на уму да обликовање прилагођеног броја не мења вредност у ћелији. Мења само начин приказа вредности. Дакле, могу изабрати да прикажем само вредност сата у ћелији, док би и даље имала првобитну вредност.

Професионални савет: Ако укупан број сати премашује 24 сата, уместо тога користите следећи формат прилагођеног броја: [хх]: мм: сс

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

Када одузмете временске вредности, Екцел враћа децимални број који представља резултирајућу временску разлику.

Пошто сваки цео број представља један дан, децимални део броја би представљао онај велики део дана који се лако може претворити у сате, минуте или секунде.

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

Претпоставимо да имате скуп података као што је приказано у наставку и желите да израчунате број сати између двоструких вредности

Испод је формула која ће вам дати временску разлику у сатима:

= (Б2-А2)*24

Горња формула ће вам дати укупан број сати протеклих између двоструких вредности.

Понекад Екцел покушава да буде од помоћи и даће вам резултате у временском формату (као што је приказано испод).

Ово можете лако претворити у формат броја тако што ћете кликнути на картицу Почетна, а у групи Број изабрати Број као формат.

Ако желите само да вратите укупан број сати који је протекао између два пута (без децималног дела), користите доњу формулу:

= ИНТ ((Б2-А2)*24)

Напомена: Ова формула функционише само када су обе временске вредности истог дана. Ако се дан промени (где је једна од временских вредности другог датума, а друга другог датума), ова формула ће дати погрешне резултате. Погледајте одељак где покривам формулу за израчунавање временске разлике када се датум промени касније у овом водичу.

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

Да бисте израчунали временску разлику у минутима, потребно је да добијену вредност помножите са укупним бројем минута у дану (што је 1440 или 24*60).

Претпоставимо да имате скуп података као што је приказано у наставку и желите да израчунате укупан број минута који је протекао између датума почетка и завршетка.

Испод је формула која ће то учинити:

= (Б2-А2)*24*60

Израчунавање временске разлике у секундама

Да бисте израчунали временску разлику у секундама, потребно је да добијену вредност помножите са укупним бројем секунди у дану (што је или 24*60*60 или 86400).

Претпоставимо да имате скуп података као што је приказано у наставку и желите да израчунате укупан број секунди које су протекле између датума почетка и завршетка.

Испод је формула која ће то учинити:

= (Б2-А2)*24*60*60

Израчунавање временске разлике помоћу функције ТЕКСТ

Још један једноставан начин да брзо добијете временску разлику без бриге о промени формата је коришћење функције ТЕКСТ.

Функција ТЕКСТ вам омогућава да наведете формат у оквиру формуле.

= ТЕКСТ (Датум завршетка - Датум почетка, формат)

Први аргумент је прорачун који желите да урадите, а други аргумент је формат у којем желите да прикажете резултат израчунавања.

Претпоставимо да имате скуп података као што је приказано испод и желите да израчунате временску разлику између два пута.

Ево неколико формула које ће вам дати резултат са различитим форматима

Прикажи само број сати:

= ТЕКСТ (Б2-А2, "хх")

Горња формула ће вам дати само резултат који приказује број сати протеклих између двоструких вредности. Ако је ваш резултат 9 сати и 30 минута, и даље ће вам приказивати само 9.

Прикажите укупан број минута

= ТЕКСТ (Б2-А2, "[мм]")

Прикажите укупан број секунди

= ТЕКСТ (Б2-А2, "[сс]")

Прикажи сате и минуте

= ТЕКСТ (Б2-А2, "[хх]: мм")

Прикажи сате, минуте и секунде

= ТЕКСТ (Б2-А2, "хх: мм: сс")

Ако се питате која је разлика између хх и [хх] у формату (или мм и [мм]), када користите угласте заграде, то ће вам дати укупан број сати између два датума, чак и ако вредност сата је већа од 24. Дакле, ако одузмете две вредности датума где је разлика већа од 24 сата, коришћењем [хх] добићете укупан број сати, а хх ће вам дати само сате протекле на дан завршног датума .

Добијте временску разлику у једној јединици (сати/минуте) и занемарите друге

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

Свака од ових функција узима један једини аргумент, што је временска вредност, и враћа задату временску јединицу.

Претпоставимо да имате скуп података као што је приказано у наставку и желите да израчунате укупан број сати и минута које су протекле између ова два времена.

Испод су формуле за то:

Израчунавање сати протеклих између два пута

= САТ (Б2-А2)

Израчунавање минута из резултата временске вредности (искључујући завршене сате)

= МИНУТА (Б2-А2)

Израчунавање секунди од резултата временске вредности (искључујући завршене сате и минуте)

= ДРУГО (Б2-А2)

Неколико ствари које морате знати када радите са овим формулама ХОУРС, МИНУТЕ и СЕЦОНД:

• Разлика између времена завршетка и времена почетка не може бити негативна (што је често случај када се датум промени). У таквим случајевима, ове формуле би вратиле #НУМ! грешка
• Ове формуле користе само временски део резултујуће временске вредности (а занемарују дневни део). Дакле, ако је разлика у крајњем времену и времену почетка 2 дана, 10 сати, 32 минуте и 44 секунде, ХОУР формула ће дати 10, МИНУТНА формула ће дати 32, а ДРУГА формула ће дати 44

Израчунајте протекло време до сада (од почетка)

Ако желите да израчунате укупно време које је протекло између времена почетка и тренутног времена, можете користити формулу САДА уместо времена завршетка.

НОВ функција враћа тренутни датум и време у ћелији у којој се користи. То је једна од оних функција које не узимају никакве улазне аргументе.

Дакле, ако желите да израчунате укупно време које је протекло између времена почетка и тренутног времена, можете користити доњу формулу:

= САДА () - Време почетка

Испод је пример где имам почетна времена у колони А, а време које је до сада протекло у колони Б.

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

То можете учинити помоћу формуле ТЕКСТ испод:

= ТЕКСТ (САД ()-А2, "дд хх: сс: мм")

Исту ствар можете постићи и променом прилагођеног обликовања ћелије (као што је описано раније у овом водичу) тако да приказује дан, као и временски део.

У случају да ваше време почетка има само временски део, Екцел би то сматрао временом 1. јануара 1990.

У овом случају, ако користите функцију САДА за израчунавање протеклог времена, то ће вам дати погрешан резултат (јер би резултирајућа вредност такође имала укупне дане који су протекли од 1. јануара 1990.).

У том случају можете користити следећу формулу:

= САДА ()- ИНТ (ОДМАХ ())- А2

Горња формула користи функцију ИНТ за уклањање дневног дела из вредности коју враћа функција нов, а то се затим користи за израчунавање временске разлике.

Имајте на уму да је САД нестабилна функција која се ажурира кад год дође до промене на радном листу, али се не ажурира у реалном времену

Израчунајте време када се датум промени (израчунајте и прикажите негативна времена у Екцелу)

Досадашње методе добро функционишу ако је ваше време завршетка касније од времена почетка.

Али проблем настаје када је ваше време завршетка ниже од времена почетка. То се често дешава када попуњавате временске листе у које уносите само време, а не цео датум и време.

У таквим случајевима, ако радите у једној ноћној смени, а датум се мења, постоји могућност да ваше време завршетка буде раније од времена почетка.

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

Ако радите само са временским вредностима, одузимањем почетног времена од завршног времена добићете негативну вредност од 9 сати (9 - 18).

И Екцел не може да обради негативне временске вредности (а што се тога тиче ни људи, осим ако не можете путовати кроз време)

У таквим случајевима потребан вам је начин да схватите да се дан променио и да прорачун треба извршити у складу с тим.

Срећом, за ово постоји заиста лако решење.

Претпоставимо да имате скуп података као што је приказано испод, где имам време почетка и време завршетка.

Као што сте приметили да је понекад време почетка увече, а време завршетка ујутру (што указује да је ово била ноћна смена и дан се променио).

Ако за израчунавање временске разлике користим доњу формулу, показаће ми се хеш знакови у ћелијама где је резултат негативна вредност (означено жутом бојом на доњој слици).

= Б2-А2

Ево ИФ формуле која, без обзира да ли је вредност временске разлике негативна или не, и у случају да је негативна, враћа прави резултат

= ИФ ((Б2-А2) <0,1- (А2-Б2), (Б2-А2))

Иако ово добро функционира у већини случајева, ипак остаје кратко у случају да је вријеме почетка и времена завршетка удаљено више од 24 сата. На пример, неко се пријављује у 9:00 ујутру првог дана, а одјављује се у 11:00 другог дана.

Пошто је ово више од 24 сата, не постоји начин да се зна да ли се особа одјавила након 2 сата или након 26 сати.

Иако би најбољи начин да се то реши било да се уверите да уноси укључују датум и време, али ако је то само време са којим радите, онда би горња формула требало да реши већину проблема ( с обзиром да је мало вероватно да ће неко радити дуже од 24 сата)

Додавање/ одузимање времена у Екцелу

До сада смо видели примере где смо имали почетак и крај времена, и морали смо да пронађемо временску разлику.

Екцел вам такође омогућава да лако додате или одузмете фиксну вредност времена од постојеће вредности датума и времена.

На пример, рецимо да имате листу задатака у реду за које сваки задатак захтева одређено време и желите да знате када ће сваки задатак завршити.

У том случају можете лако додати време које ће сваки задатак одузети времену почетка да бисте знали у које време се очекује да ће задатак бити завршен.

Пошто Екцел складишти вредности датума и времена као бројеве, морате осигурати да време које покушавате да додате поштује формат који Екцел већ прати.

На пример, ако датуму у Екцелу додате 1, добићете следећи датум. То је зато што 1 представља цео дан у Екцелу (што је једнако 24 сата).

Дакле, ако желите да додате 1 сат постојећој временској вредности, не можете да наставите и једноставно јој додате 1. морате бити сигурни да сте ту вредност сата претворили у децимални део који представља један сат. а исто важи и за додавање минута и секунди.

Коришћење функције ТИМЕ

Функција времена у Екцелу узима вредност сата, минуту и ​​секунду и претвара је у децимални број који представља ово време.

На пример, ако желим да додам 4 сата постојећем времену, могу да користим формулу испод:

= Време почетка + ВРЕМЕ (4,0,0)

Ово је корисно ако знате колико сати, минута и секунди желите додати постојећем времену и једноставно користите функцију ТИМЕ без бриге о исправној конверзији времена у децималну вриједност.

Такође имајте на уму да ће функција ТИМЕ узети у обзир само цијели број вриједности сата, минута и секунди које унесете. На пример, ако користим 5,5 сати у ТИМЕ функцији, то би додало само пет сати и занемарило децимални део.

Такође имајте на уму да функција ТИМЕ може додати само вредности које су мање од 24 сата. Ако је вредност вашег сата већа од 24, то би вам дало нетачан резултат.

Исто важи и за минуте и други део где ће функција узети у обзир само вредности које су мање од 60 минута и 60 секунди

Као што сам додао време помоћу функције ТИМЕ, тако можете и одузети време. Само промените знак + у негативан знак у горњим формулама

Коришћење основне аритметике

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

Ако желите већу контролу, можете користити аритметичку методу коју ћу овде обрадити.

Концепт је једноставан - претворите временску вредност у децималну вредност која представља део дана, а затим је можете додати било којој временској вредности у Екцелу.

На пример, ако желите да додате 24 сата постојећој вредности времена, можете користити доњу формулу:

= Старт_тиме + 24/24

То само значи да додајем један дан постојећој вредности времена.

Напредујући истим концептом, рецимо да желите да додате 30 сати временској вредности, можете користити доњу формулу:

= Старт_тиме + 30/24

Горња формула чини исту ствар, где би целобројни део (30/24) представљао укупан број дана у времену које желите да додате, а децимални део би представљао сате/минуте/секунде

Слично, ако имате одређени број минута које желите да додате временској вредности, можете користити доњу формулу:

= Старт_тиме + (Минуте за додавање)/24*60

А ако имате број секунди које желите да додате, можете користити доњу формулу:

= Старт_тиме + (минуте за додавање)/24*60*60

Иако овај метод није тако лак као коришћење функције времена, сматрам га много бољим јер ради у свим ситуацијама и следи исти концепт. за разлику од функције времена, не морате да бринете о томе да ли је време које желите да додате мање од 24 сата или више од 24 сата

Можете следити исти концепт одузимајући и време. Само промените знак + у негативан знак у горњим формулама

Како збројити време у програму Екцел

Понекад ћете можда желети да брзо саберете све временске вредности у Екцелу. Додавање више временских вредности у Екцел -у је прилично једноставно (потребна је само једноставна СУМ формула)

Али постоји неколико ствари које морате знати када додате време у Екцел, посебно формат ћелије који ће вам показати резултат.

Погледајмо пример.

Испод имам списак задатака заједно са временом које ће сваки задатак узети у колони Б, и желим брзо да додам та времена и знам укупно време које ће бити потребно за све ове задатке.

У ћелији Б9, користио сам једноставну формулу СУМ за израчунавање укупног времена које ће сви ови задаци трајати, и даје ми вредност као 18:30 (што значи да ће бити потребно 18 сати и 20 минута да се заврши сви ови задаци)

Све добро до сада!

Како збрајати преко 24 сата у Екцелу

Сада видите шта се дешава када променим време у којему ће задатак 2 бити завршен са 1 сата на 10 сати.

Резултат сада каже 03:20, што значи да би требало да прођу 3 сата и 20 минута да се заврше сви ови задаци.

Ово је нетачно (очигледно)

Проблем овде није у томе да Екцел забрља. Проблем је у томе што је ћелија обликована на такав начин да ће вам показати само временски део добијене вредности.

А пошто је овде добијена вредност већа од 24 сата, Екцел је одлучио да део од 24 сата претвори у дан, уклонивши га из вредности која се приказује кориснику, и прикаже само преостале сате, минуте и секунде.

На срећу, ово се лако решава.

Све што треба да урадите је да промените формат ћелије да бисте је приморали да приказује сате чак и ако прелази 24 сата.

Испод је неколико формата које можете користити:

Формат	Очекивани резултат
[х]: мм	28:30
[м]: сс	1710:00
д “Д” хх: мм	1 Д 04:30
д "Д" хх "Мин" сс "Сец"	1 Д 04 Мин 00 Сек
д „Дан“ хх „Минута“ сс „Секунде“	1 дан 04 минута 00 секунди

Формат можете променити тако што ћете отићи у дијалог ћелије за формат и применити прилагођени формат, или користити функцију ТЕКСТ и користити било који од горенаведених формата у самој формули

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

= ТЕКСТ (ЗБИР (Б2: Б7), "[х]: мм: сс")

или доњу формулу ако желите да сате дуже од 24 сата претворите у дане:

= ТЕКСТ (ЗБИР (Б2: Б7), "дд хх: мм: сс")

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

У неким случајевима ћете можда открити да уместо приказивања временске вредности Екцел приказује ћелијске симболе у ​​ћелији.

Ево неких могућих разлога и начина да их решите:

Колона није довољно широка

Када ћелија нема довољно простора за приказ целог датума, може приказати симболе распршивања.

Има једноставно решење - промените ширину колоне и учините је широм.

Негативна вредност датума

Вредност датума или времена не може бити негативна у програму Екцел. у случају да рачунате временску разлику и она се покаже негативном, Екцел ће вам приказати хасх симболе.

Начин поправки за промену формуле да бисте добили прави резултат. На пример, ако рачунате временску разлику између два времена и датум се мења, морате прилагодити формулу тако да то узме у обзир.

У другим случајевима можете користити функцију АБС за претварање негативне временске вредности у позитиван број како би се правилно приказала. Алтернативно, можете користити и ИФ формулу да проверите да ли је резултат негативна вредност и вратите нешто значајније.

У овом водичу обрађивао сам теме о рачунању времена у Екцелу (где можете израчунати временску разлику, додати или одузети време, приказати време у различитим форматима и сумирати временске вредности)

Надам се да вам је овај водич био користан.