Operațiuni cu data și timpul în #Excel

Acest articol propune fanilor Excel, în special de nivel începător și mediu, diferite metode de rezolvare a problemelor Excel care implică diferențe între diferite date și ore. Dacă aveți și alte exemple, formule utile, sau probleme specifice, vă rog să folosiți secțiunea de comentarii.

Fișierul cu exemplele de mai jos poate fi descărcat de aici:

https://1drv.ms/x/s!ApGubfWFh8NurNd0WHRLierdi4Ba_w?e=dziZZS

Câteva noțiuni introductive

Data în Excel este un număr care reprezintă numărul de zile trecute de la prima dată care se poate înregistra: 01.01.1900. Această dată este echivalentul numărului întreg 1.

Ora în Excel este un număr zecimal cuprins între 0,000001 și 0,999999. Cu cât folosim mai multe zecimale, cu atât timpul este mai precis. 6 zecimale sunt suficiente în opinia mea pentru a reprezenta cât mai exact timpul.

Pentru a transforma o dată într-o valoare, putem pur și simplu să schimbăm formatul celulei din Dată în Număr și se va afișa echivalentul numeric al datei. O alternativă este utilizarea funcției VALUE(celula cu data) și va rezulta un număr întreg.

Pentru a prelua data din sistem folosim funcția TODAY() iar pentru a prelua data și ora folosim funcția NOW()

Dacă dintr-o celulă care are înregistrată data și ora dorim să extragem doar data atunci vom folosi funcția INT(celula cu data) care va returna un număr întreg pe care îl putem formata ca Short Date. Dacă dorim să extragem doar timpul, atunci vom scădea din celula cu data și ora, întregul acelei celule, rezultând astfel doar partea zecimală, pe care o formatăm ca Time.

O observație de detaliu: Numărul maxim de afișare a zecimalelor în Excel este de 15, în cazul în care partea întreagă este 0. Dacă numărul din partea întreagă este mai mare ca 0 numărul de zecimale scade la 14.

În imagine, formule și reprezentări noțiuni introductive.

În cazul în care timpul este în format text, ca rezultat al unei extrageri sau import, obținerea valorii timpului se realizează cu funcția TIMEVALUE().

Diferența între două date calendaristice

Scăderea a două date calendaristice presupune obținerea unui număr întreg cu diferite semnificații prezentate în coloana de observații.

Funcția NETWORKDAYS(), implică definirea unui tabel separat cu zile festive. Aceste date vor fi scăzute din rezultatul final, dacă sunt regăsite în intervalul de date pentru care se calculează diferența.

Operațiuni de adunare

Operațiunea de adunare a două date, va returna un număr întreg fără nici o semnificație concretă.

Ca aplicabilitate economică, linia 6 din imagine poate fi utilizată în calcularea termenului de scadență al unei facturi, iar pentru linia 9, putem să ne calculăm care este ultima zi de concediu de 7 zile cu pornire de la data de 17.09.2021, excluzând zilele de sărbătoare națională din tabelul cu zile festive.

În acest exemplu funcția WORKDAY() este cea care ne ajută să adăugăm un număr de zile lucrătoare la o data specificată.

Diferența dintre două ore

Diferența între două ore este o operațiune aritmetică simplă prin care scădem ora mai mare din ora mai mică. Exemplu în C2.

Problema apare în momentul în care dorim să scădem două ore aflate în zile diferite. După cum observăm în celula C4, numărul de ore între două ore din două zile diferite este afișat 4:10. Din punct de vedere numeric timpul respectiv ca diferență dintre cele două date este 1,17. Acest 1 presupune că între cele două ore au trecut 24 de ore. Ca să putem vedea răspunsul complet al numărului de ore, putem proceda la formatarea celulei cu timpul în formatul Time – total ore. Detalii în imagine.

În cazul în care avem activități care încep într-o zi lucrătoare și se finalizează în timpul de lucru al celei de-a doua zi lucrătoare formula devine mult mai complexă, pentru că trebuie să eliminăm timpul din afara orelor de program. Având în vedere că acest timp de începere și final este variabil, propun o formulă care să se raporteze la aceste ore.

Ca să ajung la formula din C14, afișată în E14, am folosit tehnica coloanelor ajutătoare:

• C10: Am determinat diferența de zile lucrătoare între cele două date. Având în vedere că ambele sunt lucrătoare, dacă sunt consecutive sau consecutive peste weekend (vineri și luni) va rezulta valoarea 2.
• C11: Numărul de ore lucrate în prima zi ca diferență între ora de final a programului B20 și partea zecimală a valorii din prima zi. Funcția INT() ne permite scoaterea valorii întregi, iar prin diferență obținem partea zecimală, aferentă timpului.
• C12: Numărul de ore lucrate a doua zi ca diferență din ora din a doua zi minus ora de start a programului din A9.

Această formulă este funcțională atât pentru zile consecutive cât și pentru activități începute vineri și finalizate după weekend.

Dacă o activitate începe într-o zi și se termină peste două zile, formula trebuie îmbunătățită cu adăugarea numărului de zile lucrătoare înmulțit cu numărul de ore lucrătoare dintr-o zi. În cazul meu am specificat 8 ore lucrătoare pe o zi.

Cam atât pentru moment. Dacă aveți alte metode de rezolvare și doriți să le partajați cu noi puteți să le treceți în secțiunea comentarii.

Fișierul cu toate formulele și exemplele poate fi descărcat de la această adresă:

https://1drv.ms/x/s!ApGubfWFh8NurNd0WHRLierdi4Ba_w?e=dziZZS

Sper să fie util cuiva.