Calculați timpul în Excel (Diferența de timp, orele lucrate, Adăugarea / scăderea)

Deoarece datele și orele sunt stocate ca numere în partea din spate în Excel, puteți utiliza cu ușurință operații aritmetice simple și formule pentru valorile datei și orei.

De exemplu, puteți adăuga două valori de timp diferite sau valori de dată sau puteți calcula diferența de timp între două date / ore date.

În acest tutorial, vă voi arăta câteva moduri de a efectua calcule folosind timpul în Excel (cum ar fi calcularea diferenței de timp, adăugarea sau scăderea timpului, afișarea timpului în diferite formate și efectuarea unei sume de valori ale timpului)

Cum gestionează Excel data și ora?

După cum am menționat, datele și orele sunt stocate ca numere într-o celulă din Excel. Un număr întreg reprezintă o zi completă, iar partea zecimală a unui număr ar reprezenta partea zilei (care poate fi convertită în valori de ore, minute și secunde)

De exemplu, valoarea 1 reprezintă 01 ianuarie 1900 în Excel, care este punctul de plecare de la care Excel începe să ia în considerare datele.

Deci, 2 ar însemna 02 ianuarie 1990, 3 ar însemna 03 ianuarie 1900 și așa mai departe, iar 44197 ar însemna 1 ianuarie 2021.

Notă: Excel pentru Windows și Excel pentru Mac urmează date de începere diferite. 1 în Excel pentru Windows ar însemna 1 ianuarie 1900 și 1 în Excel pentru Mac ar însemna 1 ianuarie 1904

Dacă există cifre după un punct zecimal în aceste numere, Excel le-ar lua în considerare ca parte a zilei și poate fi convertit în ore, minute și secunde.

De exemplu, 44197,5 ar însemna 01 ianuarie 2021 12:00:00 PM.

Deci, dacă lucrați cu valori de timp în Excel, ați lucra în esență cu porțiunea zecimală a unui număr.

Și Excel vă oferă flexibilitatea de a converti acea porțiune zecimală în diferite formate, cum ar fi numai ore, numai minute, numai secunde sau o combinație de ore, minute și secunde.

Acum că înțelegeți modul în care este stocat timpul în Excel, să aruncăm o privire la câteva exemple despre cum să calculați diferența de timp între două date sau ore diferite în Excel

Formule pentru calcularea diferenței de timp între două ori

În multe cazuri, tot ce doriți să faceți este să aflați timpul total care a trecut între valorile de două ori (cum ar fi în cazul unei foi de timp care are In-time și Out-time).

Metoda pe care o alegeți va depinde de modul în care timpul este menționat într-o celulă și în ce format doriți rezultatul.

Să aruncăm o privire la câteva exemple

Scăderea simplă a calculării diferenței de timp în Excel

Deoarece timpul este stocat ca număr în Excel, găsiți diferența dintre 2 valori de timp, puteți scădea cu ușurință ora de început din ora de sfârșit.

Ora de sfârșit - Ora de început

Rezultatul scăderii ar fi, de asemenea, o valoare zecimală care ar reprezenta timpul care a trecut între cele două valori de timp.

Mai jos este un exemplu în care am ora de început și de sfârșit și am calculat diferența de timp cu o simplă scădere.

Există posibilitatea ca rezultatele dvs. să fie afișate în formatul orei (în loc de zecimale sau în valori de ore / minute). În exemplul nostru de mai sus, rezultatul din celula C2 arată 09:30 AM în loc de 9.5.

Este perfect, deoarece Excel încearcă să copieze formatul din coloana alăturată.

Pentru a converti acest lucru într-o zecimală, schimbați formatul celulelor în General (opțiunea este în fila Acasă din grupul Numere)

Odată ce ați obținut rezultatul, îl puteți formata în moduri diferite. De exemplu, puteți afișa valoarea numai în ore sau numai în minute sau o combinație de ore, minute și secunde.

Mai jos sunt diferite formate pe care le puteți utiliza:

Format	Ce face
h	Afișează doar orele trecute între cele două date
hh	Afișează orele în două cifre (cum ar fi 04 sau 12)
hh: mm	Afișează ore și minute trecute între cele două date, cum ar fi 10:20
hh: mm: ss	Afișează ore, minute și secunde trecute între cele două date, cum ar fi 10:20:36

Și dacă vă întrebați unde și cum să aplicați aceste formate de dată personalizate, urmați pașii de mai jos:

1. Selectați celulele în care doriți să aplicați formatul de dată
2. Țineți apăsată tasta Control și apăsați tasta 1 (sau Comanda + 1 dacă utilizați Mac)
3. În caseta de dialog Formatare celule care se deschide, faceți clic pe fila Număr (dacă nu este deja selectată)
4. În panoul din stânga, faceți clic pe Personalizat
5. Introduceți oricare dintre codurile de format dorite în câmpul Tip (în acest exemplu, folosesc hh: mm: ss)
6. Faceți clic pe OK

Pașii de mai sus ar schimba formatarea și ar arăta valoarea pe baza formatului.

Rețineți că formatarea numerelor personalizate nu modifică valoarea din celulă. Modifică doar modul în care este afișată o valoare. Așadar, pot alege să afișez doar valoarea orei într-o celulă, în timp ce ar avea în continuare valoarea inițială.

Sfat pentru profesioniști: dacă numărul total de ore depășește 24 de ore, utilizați în schimb următorul format de număr personalizat: [hh]: mm: ss

Calculați diferența de timp în ore, minute sau secunde

Când scădeți valorile timpului, Excel returnează un număr zecimal care reprezintă diferența de timp rezultată.

Deoarece fiecare număr întreg reprezintă o zi, partea zecimală a numărului ar reprezenta acea parte a zilei care poate fi ușor convertită în ore sau minute sau secunde.

Calculul diferenței de timp în ore

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să calculați numărul de ore dintre valorile de două ori

Mai jos este formula care vă va oferi diferența de timp în ore:

= (B2-A2) * 24

Formula de mai sus vă va oferi numărul total de ore trecute între valorile de două ori.

Uneori, Excel încearcă să vă fie de ajutor și vă va oferi rezultatul și în format de timp (așa cum se arată mai jos).

Puteți converti cu ușurință acest lucru în format Număr făcând clic pe fila Acasă și, în grupul Număr, selectând Număr ca format.

Dacă doriți doar să returnați numărul total de ore scurs între cele două ori (fără nici o parte zecimală), utilizați formula de mai jos:

= INT ((B2-A2) * 24)

Notă: Această formulă funcționează numai atunci când ambele valori ale timpului sunt din aceeași zi. Dacă ziua se schimbă (unde una dintre valorile orei este de altă dată și a doua de altă dată), această formulă va da rezultate greșite. Aruncați o privire la secțiunea în care acoper formula pentru a calcula diferența de timp când data se schimbă mai târziu în acest tutorial.

Calculul diferenței de timp în minute

Pentru a calcula diferența de timp în minute, trebuie să multiplicați valoarea rezultată cu numărul total de minute într-o zi (care este 1440 sau 24 * 60).

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să calculați numărul total de minute scurs între data de începere și data de încheiere.

Mai jos este formula care va face acest lucru:

= (B2-A2) * 24 * 60

Calculul diferenței de timp în secunde

Pentru a calcula diferența de timp în secunde, trebuie să înmulțiți valoarea rezultată cu numărul total de secunde într-o zi (care este sau 24 * 60 * 60 sau 86400).

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să calculați numărul total de secunde care au trecut între data de începere și data de încheiere.

Mai jos este formula care va face acest lucru:

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

Calculul diferenței de timp cu funcția TEXT

O altă modalitate ușoară de a obține rapid diferența de timp fără a vă face griji cu privire la schimbarea formatului este utilizarea funcției TEXT.

Funcția TEXT vă permite să specificați formatul chiar în formulă.

= TEXT (Data de încheiere - Data de începere, format)

Primul argument este calculul pe care doriți să îl faceți, iar al doilea argument este formatul în care doriți să arătați rezultatul calculului.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să calculați diferența de timp dintre cele două ori.

Iată câteva formule care vă vor oferi rezultatul cu diferite formate

Afișați doar numărul de ore:

= TEXT (B2-A2, „hh”)

Formula de mai sus vă va oferi doar rezultatul care arată numărul de ore scurs între valorile de două ori. Dacă rezultatul dvs. este de 9 ore și 30 de minute, acesta va afișa în continuare doar 9.

Afișați numărul de minute totale

= TEXT (B2-A2, „[mm]”)

Afișați numărul de secunde totale

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

Afișați orele și minutele

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

Afișați ore, minute și secunde

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

Dacă vă întrebați care este diferența dintre hh și [hh] în format (sau mm și [mm]), atunci când utilizați parantezele pătrate, acesta vă va oferi numărul total de ore dintre cele două date, chiar dacă valoarea orei este mai mare de 24. Deci, dacă scădeți două valori de dată în care diferența este mai mare de 24 de ore, folosind [hh] vă va oferi numărul total de ore, iar hh vă va oferi doar orele trecute în ziua datei de încheiere .

Obțineți diferența de timp într-o unitate (ore / minute) și ignorați pe alții

Dacă doriți să calculați diferența de timp dintre cele două valori de timp doar în numărul de ore sau minute sau secunde, atunci puteți utiliza funcția dedicată ORA, MINUT sau SECOND.

Fiecare dintre aceste funcții ia un singur argument, care este valoarea timpului și returnează unitatea de timp specificată.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să calculați numărul total de ore minute și secunde care au trecut între aceste două ori.

Mai jos sunt formulele pentru a face acest lucru:

Calculul orelor scurs între două ori

= ORA (B2-A2)

Calcularea minutelor din rezultatul valorii timpului (cu excepția orelor finalizate)

= MINUT (B2-A2)

Calcularea secundelor din rezultatul valorii timpului (cu excepția orelor și minutelor finalizate)

= SECOND (B2-A2)

Câteva lucruri pe care trebuie să le știți atunci când lucrați cu aceste formule ORE, MINUTE și AL DOILEA:

• Diferența dintre ora de sfârșit și ora de începere nu poate fi negativă (ceea ce se întâmplă adesea când data se schimbă). În astfel de cazuri, aceste formule ar returna un #NUM! eroare
• Aceste formule folosesc doar porțiunea de timp a valorii de timp rezultate (și ignoră porțiunea de zi). Deci, dacă diferența dintre ora de încheiere și ora de începere este de 2 zile, 10 ore, 32 de minute și 44 de secunde, formula ORE va da 10, formula MINUTUL va da 32 și a doua formulă va da 44

Calculați timpul scurs până acum (de la ora de început)

Dacă doriți să calculați timpul total care a trecut între ora de început și ora curentă, puteți utiliza formula ACUM în loc de ora de sfârșit.

Funcția ACUM returnează data curentă și ora din celula în care este utilizată. Este una dintre acele funcții care nu ia niciun argument de intrare.

Deci, dacă doriți să calculați timpul total care a trecut între ora de început și ora curentă, puteți utiliza formula de mai jos:

= ACUM () - Ora de început

Mai jos este un exemplu în care am timpii de început în coloana A și timpul scurs până acum în coloana B.

Dacă diferența de timp între data și ora de începere și ora curentă este mai mare de 24 de ore, atunci puteți formata rezultatul pentru a afișa ziua, precum și porțiunea de timp.

Puteți face acest lucru folosind formula TEXT de mai jos:

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

De asemenea, puteți obține același lucru schimbând formatarea personalizată a celulei (așa cum am discutat mai devreme în acest tutorial), astfel încât să afișeze ziua, precum și porțiunea de timp.

În cazul în care ora de început are doar porțiunea de timp, atunci Excel ar considera-o ca ora de la 1 ianuarie 1990.

În acest caz, dacă utilizați funcția ACUM pentru a calcula timpul scurs până acum, acesta vă va da rezultatul greșit (deoarece valoarea rezultată ar avea, de asemenea, numărul total de zile care au trecut de la 1 ianuarie 1990).

Într-un astfel de caz, puteți utiliza formula de mai jos:

= ACUM () - INT (ACUM ()) - A2

Formula de mai sus folosește funcția INT pentru a elimina porțiunea de zi din valoarea returnată de funcția acum și aceasta este apoi utilizată pentru a calcula diferența de timp.

Rețineți că ACUM este o funcție volatilă care se actualizează ori de câte ori există o modificare a foii de lucru, dar nu se actualizează în timp real

Calculați ora când se schimbă data (calculați și afișați timpi negativi în Excel)

Metodele acoperite până acum funcționează bine dacă ora de sfârșit este mai târziu decât ora de început.

Dar problema apare atunci când ora de sfârșit este mai mică decât ora de început. Acest lucru se întâmplă adesea atunci când completați foi de timp în care introduceți doar ora și nu întreaga dată și oră.

În astfel de cazuri, dacă lucrați într-o tură de o noapte și data se schimbă, există posibilitatea ca ora de sfârșit să fie mai devreme decât ora de începere.

De exemplu, dacă începeți munca la 18:00 seara și vă terminați munca și timpul liber la 9:00 dimineața.

Dacă lucrați numai cu valori de timp, atunci scăderea timpului de început din ora de sfârșit vă va oferi o valoare negativă de 9 ore (9 - 18).

Și Excel nu poate gestiona valori de timp negative (și, de altfel, nici oamenii nu pot, dacă nu puteți călători în timp)

În astfel de cazuri, aveți nevoie de o modalitate de a vă da seama că ziua s-a schimbat și calculul ar trebui făcut în consecință.

Din fericire, există o soluție foarte ușoară pentru acest lucru.

Să presupunem că aveți un set de date așa cum se arată mai jos, unde am ora de început și ora de sfârșit.

Așa cum ați observa că uneori ora de început este seara, iar ora de sfârșit este dimineața (ceea ce indică faptul că aceasta a fost o schimbare peste noapte și ziua s-a schimbat).

Dacă folosesc formula de mai jos pentru a calcula diferența de timp, îmi va arăta semnele hash din celulele în care rezultatul este o valoare negativă (evidențiată în galben în imaginea de mai jos).

= B2-A2

Iată o formulă IF care, indiferent dacă valoarea diferenței de timp este negativă sau nu, și în cazul în care este negativă, atunci returnează rezultatul corect

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

Deși acest lucru funcționează bine în majoritatea cazurilor, acesta rămâne scurt în cazul în care ora de începere și ora de sfârșit sunt mai mari de 24 de ore. De exemplu, cineva se conectează la ora 9:00 în ziua 1 și se deconectează la ora 11:00 în ziua 2.

Deoarece aceasta depășește 24 de ore, nu există nicio modalitate de a ști dacă persoana s-a deconectat după 2 ore sau după 26 de ore.

Deși cel mai bun mod de a aborda acest lucru ar fi să vă asigurați că intrările includ data și ora, dar dacă este doar ora cu care lucrați, atunci formula de mai sus ar trebui să se ocupe de majoritatea problemelor ( considerând că este puțin probabil ca cineva să lucreze mai mult de 24 de ore)

Adăugarea / scăderea timpului în Excel

Până în prezent, am văzut exemple în care am avut ora de început și de sfârșit și a trebuit să găsim diferența de timp.

Excel vă permite, de asemenea, să adăugați sau să scăpați cu ușurință o valoare de timp fixă ​​din data și ora existente.

De exemplu, să presupunem că aveți o listă de sarcini în coadă în care fiecare sarcină durează timpul specificat și doriți să știți când se va încheia fiecare sarcină.

Într-un astfel de caz, puteți adăuga cu ușurință timpul pe care fiecare sarcină îl va lua până la ora de început pentru a afla la ce oră se așteaptă finalizarea sarcinii.

Deoarece Excel stochează valorile datei și orei ca numere, trebuie să vă asigurați că timpul pe care încercați să îl adăugați respectă formatul pe care îl urmează deja Excel.

De exemplu, dacă adăugați 1 la o dată în Excel, aceasta vă va oferi următoarea dată. Acest lucru se datorează faptului că 1 reprezintă o zi întreagă în Excel (care este egală cu 24 de ore).

Deci, dacă doriți să adăugați 1 oră la o valoare de timp existentă, nu puteți continua și pur și simplu adăugați 1 la ea. trebuie să vă asigurați că convertiți acea valoare a orei în porțiunea zecimală care reprezintă o oră. și același lucru este valabil pentru adăugarea de minute și secunde.

Utilizarea funcției TIME

Funcția de timp în Excel ia valoarea orei, valoarea minutelor și valoarea secundelor și o convertește într-un număr zecimal care reprezintă această dată.

De exemplu, dacă vreau să adaug 4 ore la o oră existentă, pot folosi formula de mai jos:

= Start Time + TIME (4,0,0)

Acest lucru este util dacă știți câte ore, minute și secunde doriți să adăugați la un timp existent și pur și simplu utilizați funcția TIME fără să vă faceți griji cu privire la conversia corectă a timpului într-o valoare zecimală.

De asemenea, rețineți că funcția TIME va lua în considerare doar partea întreagă a valorii orei, minutelor și secundelor pe care ați introdus-o. De exemplu, dacă folosesc 5,5 ore în funcția TIME, ar adăuga doar cinci ore și ar ignora partea zecimală.

De asemenea, rețineți că funcția TIME poate adăuga doar valori mai mici de 24 de ore. Dacă valoarea orei dvs. este mai mare de 24, acest lucru vă va oferi un rezultat incorect.

Și același lucru este valabil cu minutele și partea a doua, unde funcția va lua în considerare numai valori mai mici de 60 de minute și 60 de secunde

La fel cum am adăugat timp folosind funcția TIME, puteți scădea și timpul. Schimbați semnul + într-un semn negativ în formulele de mai sus

Utilizarea aritmeticii de bază

Atunci când funcția de timp este ușor și convenabil de utilizat, aceasta vine cu câteva restricții (așa cum este prezentat mai sus).

Dacă doriți mai mult control, puteți folosi metoda aritmetică pe care o voi acoperi aici.

Conceptul este simplu - convertiți valoarea timpului într-o valoare zecimală care reprezintă porțiunea zilei și apoi o puteți adăuga la orice valoare de timp din Excel.

De exemplu, dacă doriți să adăugați 24 de ore la o valoare de timp existentă, puteți utiliza formula de mai jos:

= Start_time + 24/24

Aceasta înseamnă doar că adaug o zi la valoarea de timp existentă.

Acum, luând același concept înainte, să presupunem că doriți să adăugați 30 de ore la o valoare de timp, puteți utiliza formula de mai jos:

= Start_time + 30/24

Formula de mai sus face același lucru, în care partea întreagă a (30/24) ar reprezenta numărul total de zile din timpul pe care doriți să îl adăugați, iar partea zecimală ar reprezenta ore / minute / secunde

În mod similar, dacă aveți un anumit număr de minute pe care doriți să îl adăugați la o valoare de timp, puteți utiliza formula de mai jos:

= Start_time + (Minutes to Add) / 24 * 60

Și dacă aveți numărul de secunde pe care doriți să îl adăugați, atunci puteți utiliza formula de mai jos:

= Start_time + (Minutes to Add) / 24 * 60 * 60

Deși această metodă nu este la fel de ușoară ca utilizarea funcției timp, o găsesc mult mai bună, deoarece funcționează în toate situațiile și urmează același concept. spre deosebire de funcția de timp, nu trebuie să vă faceți griji dacă timpul pe care doriți să îl adăugați este mai mic de 24 de ore sau mai mult de 24 de ore

Puteți urmări același concept în timp ce scadeți și timpul. Pur și simplu schimbați + într-un semn negativ în formulele de mai sus

Cum să SUMAȚI timpul în Excel

Uneori poate doriți să adăugați rapid toate valorile de timp în Excel. Adăugarea mai multor valori de timp în Excel este destul de simplă (este nevoie doar de o formulă simplă SUM)

Dar există câteva lucruri pe care trebuie să le știți când adăugați timp în Excel, în special formatul de celulă care vă va arăta rezultatul.

Să aruncăm o privire la un exemplu.

Mai jos am o listă de sarcini, împreună cu timpul pe care fiecare activitate îl va lua în coloana B și vreau să adaug rapid aceste ore și să știu timpul total pe care îl va lua pentru toate aceste sarcini.

În celula B9, am folosit o formulă simplă SUM pentru a calcula timpul total pe care urmează să-l ia toate aceste sarcini și îmi dă valoarea la 18:30 (ceea ce înseamnă că va dura 18 ore și 20 de minute pentru a finaliza toate aceste sarcini)

Toate bune până acum!

Cum să SUMAȚI peste 24 de ore în Excel

Acum vedeți ce se întâmplă când mă schimb în momentul în care va dura sarcina 2 să se termine de la 1 oră la 10 ore.

Rezultatul spune acum 03:20, ceea ce înseamnă că ar trebui să dureze 3 ore și 20 de minute pentru a finaliza toate aceste sarcini.

Acest lucru este incorect (evident)

Problema aici nu este că Excel deranjează. Problema aici este că celula este formatată în așa fel încât să vă arate doar porțiunea de timp a valorii rezultate.

Și întrucât valoarea rezultată aici este mai mare de 24 de ore, Excel a decis să transforme partea de 24 de ore într-o zi, să o elimine din valoarea care este afișată utilizatorului și să afișeze doar restul de ore, minute și secunde.

Din fericire, acest lucru are o soluție ușoară.

Tot ce trebuie să faceți este să modificați formatul celulei pentru a-l forța să afișeze ore chiar dacă depășește 24 de ore.

Mai jos sunt câteva formate pe care le puteți utiliza:

Format	rezultat asteptat
[h]: mm	28:30
[m]: ss	1710:00
d „D” hh: mm	1 J 04:30
d „D” hh „Min” ss „Sec”	1 D 04 Min 00 Sec
d „Ziua” hh „Minutul” ss „Secunde”	1 zi 04 minut 00 secunde

Puteți schimba formatul accesând caseta de dialog a celulelor de formatare și aplicând formatul personalizat, sau utilizați funcția TEXT și utilizați oricare dintre formatele de mai sus în formula în sine

Puteți utiliza formula TEXT de mai jos pentru a afișa ora, chiar și atunci când sunt mai mult de 24 de ore:

= TEXT (SUMA (B2: B7), „[h]: mm: ss”)

sau formula de mai jos dacă doriți să convertiți orele care depășesc 24 de ore în zile:

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

Rezultatele afișează Hash (###) în loc de dată / oră (motive + remediere)

În unele cazuri, puteți constata că, în loc să vă arate valoarea timpului, Excel afișează simbolurile hash în celulă.

Iată câteva motive posibile și modalitățile de a le remedia:

Coloana nu este suficient de largă

Atunci când o celulă nu are suficient spațiu pentru a afișa data completă, poate afișa simbolurile hash.

Are o soluție ușoară - modificați lățimea coloanei și faceți-o mai largă.

Valoarea datei negative

O valoare de dată sau oră nu poate fi negativă în Excel. în cazul în care calculați diferența de timp și se dovedește a fi negativ, Excel vă va arăta simboluri hash.

Modul de remedieri pentru a schimba formula pentru a vă oferi rezultatul potrivit. De exemplu, dacă calculați diferența de timp dintre cele două ori și data se modifică, trebuie să ajustați formula pentru a o lua în considerare.

În alte cazuri, puteți utiliza funcția ABS pentru a converti valoarea negativă a timpului într-un număr pozitiv, astfel încât să fie afișată corect. Alternativ, puteți, de asemenea, o formulă IF pentru a verifica dacă rezultatul este o valoare negativă și pentru a returna ceva mai semnificativ.

În acest tutorial, am tratat subiecte despre calcularea timpului în Excel (unde puteți calcula diferența de timp, adăugați sau scădea timpul, puteți arăta timpul în diferite formate și suma valorilor timpului)

Sper că ți s-a părut util acest tutorial.