Tutorial Excel: Calcul DateTime (calculați diferența, vârsta, adăugarea/scăderea)
În Excel, calcularea datei și orei este utilizată frecvent, cum ar fi calcularea diferenței dintre două date/ore, adăugarea sau scăderea datei și orele, obținerea vârstei pe baza datei nașterii și așa mai departe. Aici, în acest tutorial, listează aproape scenarii despre calculul datei și vă oferă metodele aferente.
În acest tutorial, creez câteva exemple pentru a explica metodele, puteți schimba referințele de care aveți nevoie atunci când utilizați codul sau formulele VBA de mai jos
1. Calculați diferența dintre două date/ore
Calcularea diferenței între două date sau două ori poate fi cea mai normală problemă a calculării datei și orei pe care o întâlniți în munca zilnică cu Excel. Următoarele exemple de mai jos vă pot ajuta să îmbunătățiți eficiența atunci când întâmpinați aceleași probleme.
1.11 Calculați diferența dintre două date în zile/luni/ani/săptămâni
Funcția Excel DATEDIF poate fi utilizată pentru a calcula rapid diferența dintre două date în zile, luni, ani și săptămâni.
Click pentru mai multe detalii despre DATEDIF funcţie
Diferența de zile între două date
Pentru a obține diferența de zile dintre două date din celula A2 și B2, vă rugăm să utilizați formula ca aceasta
=DATEDIF(A2,B2,"d")
Anunturi Intrați cheie pentru a obține rezultatul.
Diferența de luni între două date
Pentru a obține diferența de luni între două date din celula A5 și B5, vă rugăm să utilizați formula ca aceasta
=DATEDIF(A5,B5,"m")
Anunturi Intrați cheie pentru a obține rezultatul.
Diferența de ani între două date
Pentru a obține diferența de ani dintre două date din celula A8 și B8, vă rugăm să utilizați formula ca aceasta
=DATEDIF(A8,B8,"y")
Anunturi Intrați cheie pentru a obține rezultatul.
Diferență de săptămâni între două întâlniri
Pentru a obține diferența de săptămâni dintre două date din celula A11 și B11, vă rugăm să utilizați formula ca aceasta
=DATEDIF(A11,B11,"d")/7
Anunturi Intrați cheie pentru a obține rezultatul.
Notă:
1) Când utilizați formula de mai sus pentru a obține diferența de săptămâni, poate returna un rezultat în format de dată, trebuie să formatați rezultatul la general sau la număr după cum aveți nevoie.
2) Când utilizați formula de mai sus pentru a obține diferența de săptămâni, se poate întoarce la un număr zecimal, dacă doriți să obțineți numărul întreg al săptămânii, puteți adăuga funcția ROUNDDOWN înainte, așa cum se arată mai jos, pentru a obține diferența între numărul întreg de săptămâni:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calculați luni ignoră ani și zile între două date
Dacă doriți doar să calculați diferența de luni ignorând anii și zilele dintre două date, așa cum arată captura de ecran de mai jos, iată o formulă care vă poate ajuta.
=DATEDIF(A2,B2,"ym")
Anunturi Intrați cheie pentru a obține rezultatul.
A2 este data de început, iar B2 este data de încheiere.
1.13 Calculați zilele ignorați anii și lunile dintre două date
Dacă doriți doar să calculați diferența de zile ignorând anii și lunile dintre două date, așa cum se arată mai jos, iată o formulă care vă poate ajuta.
=DATEDIF(A5,B5,"md")
Anunturi Intrați cheie pentru a obține rezultatul.
A5 este data de început, iar B5 este data de încheiere.
1.14 Calculați diferența dintre două date și returnați ani, luni și zile
Dacă doriți să obțineți diferența dintre două date și să returnați xx ani, xx luni și xx zile, așa cum arată captura de ecran de mai jos, iată și o formulă furnizată.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Anunturi Intrați cheie pentru a obține rezultatul.
A8 este data de început, iar B8 este data de încheiere.
1.15 Calculați diferența dintre o dată și astăzi
Pentru a calcula automat diferența dintre o dată și astăzi, trebuie doar să schimbați data_finală din formulele de mai sus la TODAY(). Aici ia pentru a calcula diferența de zile dintre o dată trecută și cea de azi ca exemplu.
=DATEDIF(A11,TODAY(),"d")
Anunturi Intrați cheie pentru a obține rezultatul.
notițe: dacă doriți să calculați diferența dintre o dată viitoare și azi, schimbați data_de_început în azi și luați data viitoare ca dată_de_sfârșit astfel:
=DATEDIF(TODAY(),A14,"d")
Observați că start_date trebuie să fie mai mică decât end_date în funcția DATEDIF, în caz contrar, va reveni la #NUM! valoare de eroare.
1.16 Calculați zilele lucrătoare cu sau fără vacanță între două date
Uneori, poate fi necesar să numărați numărul de zile lucrătoare cu sau fără sărbători între două date date.
În această parte, veți folosi funcția NETWORKDAYS.INTL:
Clic NETWORKDAYS.INTL pentru a-i cunoaște argumentele și utilizarea.
Numărați zilele lucrătoare cu sărbători
Pentru a număra zilele lucrătoare cu sărbători între două date în celula A2 și B2, vă rugăm să utilizați formula astfel:
=NETWORKDAYS.INTL(A2,B2)
Anunturi Intrați cheie pentru a obține rezultatul.
Numărați zilele lucrătoare fără sărbători
Pentru a număra zilele lucrătoare cu sărbători între două date în celula A2 și B2 și excluzând sărbătorile din intervalul D5:D9, vă rugăm să utilizați formula astfel:
=ZILEREȚEA.INTL(A5,B5,1,D5:D9)
Anunturi Intrați cheie pentru a obține rezultatul.
Notă:
În formulele de mai sus, ele iau sâmbăta și duminica ca weekend, dacă aveți zile diferite de weekend, vă rugăm să schimbați argumentul [weekend] după cum aveți nevoie.
1.17 Calculați weekendurile între două date
Dacă doriți să numărați numărul de weekenduri între două date, funcțiile SUMPRODUCT sau SUM vă pot face o favoare.
Pentru a număra weekendurile (sâmbătă și duminică) dintre două date din celula A12 și B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Anunturi Intrați cheie pentru a obține rezultatul.
1.18 Calculați o anumită zi a săptămânii între două date
Pentru a număra numărul unei anumite zile a săptămânii, cum ar fi luni, între două date, combinația de funcții INT și WEEKDAY vă poate ajuta.
Celula A15 și B15 sunt cele două date între care doriți să numărați luni, vă rugăm să utilizați formula astfel:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Anunturi Intrați cheie pentru a obține rezultatul.
Schimbați numărul zilei săptămânii în funcția WEEKDAY pentru a număra o zi a săptămânii diferită:
1 este duminică, 2 este luni, 3 este marți, 4 este miercuri, 5 este joi, 6 este vineri și 7 este sâmbătă)
1.19 Calculați zilele rămase în lună/an
Uneori, poate doriți să aflați zilele rămase din lună sau din an, pe baza datei furnizate, așa cum arată captura de ecran de mai jos:
Obțineți zilele rămase din luna curentă
Clic EOLUNA să cunoască argumentul și utilizarea.
Pentru a obține zilele rămase ale lunii curente în celula A2, vă rugăm să utilizați formula astfel:
=EOMONTH(A2,0)-A2
Anunturi Intrați tasta și trageți mânerul de umplere automată pentru a aplica această formulă altor celule, dacă este necesar.
Sfat: rezultatele pot fi afișate ca format de dată, doar schimbați-le ca format general sau numeric.
Obțineți zilele rămase în anul curent
Pentru a obține zilele rămase ale anului curent în celula A2, vă rugăm să utilizați formula astfel:
=DATE(YEAR(A2),12,31)-A2
Anunturi Intrați tasta și trageți mânerul de umplere automată pentru a aplica această formulă altor celule, dacă este necesar.
1.21 Calculați diferența dintre doi timpi
Pentru a face diferența dintre doi timpi, iată două formule simple care te pot ajuta.
Presupunând că în celula A2 și B2 conțin start_time și end_time separat, folosind formulele ca acestea:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Anunturi Intrați cheie pentru a obține rezultatul.
Notă:
- Dacă utilizați end_time-start_time, puteți formata rezultatul într-un alt format de timp după cum aveți nevoie în dialogul Format Cells.
- Dacă utilizați TEXT(end_time-first_time,"time_format"), introduceți formatul de timp în care doriți ca rezultatul să fie afișat în formulă, cum ar fi TEXT(end_time-first_time,"h") returnează 16.
- Dacă end_time este mai mic decât start_time, ambele formule returnează valori de eroare. Pentru a rezolva această problemă, puteți adăuga ABS în fața acestor formule, cum ar fi ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), apoi formatați rezultatul ca timp.
1.22 Calculați diferența dintre două ori în ore/minute/secunde
Dacă doriți să calculați diferența dintre două ori în ore, minute sau secunde, așa cum arată captura de ecran de mai jos, vă rugăm să urmați această parte.
Obțineți diferența de ore între două ori
Pentru a obține diferența de ore între două ori în A5 și B5, vă rugăm să utilizați formula astfel:
=INT((B5-A5)*24)
Anunturi Intrați tasta, apoi formatați rezultatul formatului de timp ca general sau număr.
Dacă doriți să obțineți diferența de ore zecimale, utilizați (end_time-start_time)*24.
Obțineți diferență de minute între două ori
Pentru a obține diferența de minute între două ori în A8 și B8, vă rugăm să utilizați formula astfel:
=INT((B8-A8)*1440)
Anunturi Intrați tasta, apoi formatați rezultatul formatului de timp ca general sau număr.
Dacă doriți să obțineți diferența de minute zecimale, utilizați (end_time-start_time)*1440.
Obțineți diferența de secunde între două ori
Pentru a obține diferența de secunde între două ori în A5 și B5, vă rugăm să utilizați formula astfel:
=(B11-A11)*86400)
Anunturi Intrați tasta, apoi formatați rezultatul formatului de timp ca general sau număr.
1.23 Calculați diferența de ore numai între două ori (nu depăși 24 de ore)
Dacă diferența dintre doi timpi nu depășește 24 de ore, funcția HOUR poate obține rapid diferența de ore dintre aceste două timpi.
Clic HOUR pentru mai multe detalii despre această funcție.
Pentru a obține diferența de ore între orele din celula A14 și B14, vă rugăm să utilizați funcția HOUR astfel:
=HOUR(B14-A14)
Anunturi Intrați cheie pentru a obține rezultatul.
Start_time trebuie să fie mai mic decât end_time, în caz contrar, formula returnează #NUM! valoare de eroare.
1.24 Calculați diferența de minute numai între două ori (nu depăși 60 de minute)
Funcția MINUT poate obține rapid doar diferența de minute dintre acești doi timpi și poate ignora orele și secundele.
Clic MINUTE pentru mai multe detalii despre această funcție.
Pentru a obține doar diferența de minute între orele din celula A17 și B17, vă rugăm să utilizați funcția MINUT astfel:
=MINUTE(B17-A17)
Anunturi Intrați cheie pentru a obține rezultatul.
Start_time trebuie să fie mai mic decât end_time, în caz contrar, formula returnează #NUM! valoare de eroare.
1.25 Calculați diferența de secunde numai între două ori (nu depăși 60 de secunde)
Funcția SECOND poate obține rapid doar diferența de secunde dintre aceste două timpi și poate ignora orele și minutele.
Clic A DOUA pentru mai multe detalii despre această funcție.
Pentru a obține doar diferența de secunde dintre timpii din celula A20 și B20, vă rugăm să utilizați funcția SECOND astfel:
=SECOND(B20-A20)
Anunturi Intrați cheie pentru a obține rezultatul.
Start_time trebuie să fie mai mic decât end_time, în caz contrar, formula returnează #NUM! valoare de eroare.
1.26 Calculați diferența dintre două ori și ore de întoarcere, minute, secunde
Dacă doriți să afișați diferența între două ori ca xx ore xx minute xx secunde, vă rugăm să utilizați funcția TEXT așa cum se arată mai jos:
Clic TEXT pentru a realiza argumentele și utilizarea acestei funcții.
Pentru a calcula diferența dintre timpii din celula A23 și B23, utilizați formula astfel:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Anunturi Intrați cheie pentru a obține rezultatul.
Notă:
Această formulă calculează, de asemenea, doar diferența de ore care nu depășește 24 de ore, iar ora de încheiere trebuie să fie mai mare decât ora de început, în caz contrar, returnează #VALOR! valoare de eroare.
1.27 Calculați diferența dintre două date și ore
Dacă există două ori în formatul mm/zz/aaaa hh:mm:ss, pentru a calcula diferența dintre ele, puteți utiliza una dintre formulele de mai jos după cum aveți nevoie.
Obțineți diferența de timp între două date și ore și returnați rezultatul în format hh:mm:ss
Luați două date și ore în celula A2 și B2 ca exemplu, vă rugăm să utilizați formula astfel:
=B2-A2
Anunturi Intrați cheie, returnând un rezultat în format datetime, apoi formatați acest rezultat ca [h]: mm: ss în categoria personalizată sub Număr în tab Celule de format dialog.
Obțineți diferența între două date și ore de întoarcere, zile, ore, minute, secunde
Luați două date și ore în celula A5 și B5 ca exemplu, vă rugăm să utilizați formula astfel:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Anunturi Intrați cheie pentru a obține rezultatul.
Notă: în ambele formule, end_datetime trebuie să fie mai mare decât start_datetime, în caz contrar, formulele returnează valori de eroare.
1.28 Calculați diferența de timp cu milisecunde
În primul rând, trebuie să știți cum să formatați celula pentru a afișa milisecunde:
Selectați celulele pe care doriți să le afișați în milisecunde și dreapta pentru a le selecta Celule de format pentru a activa funcția Celule de format , selectați pachet personalizat în Categorii lista din fila Număr și tastați acest lucru hh: mm: ss.000 în caseta de text.
Folosiți formula:
Aici pentru a calcula diferența dintre două ori în celula A8 și B8, utilizați formula ca:
=ABS(B8-A8)
Anunturi Intrați cheie pentru a obține rezultatul.
1.29 Calculați orele de lucru între două date, excluzând weekend-urile
Uneori, poate fi necesar să numărați orele de lucru între două date, excluzând weekend-urile (sâmbătă și duminică).
Aici orele de lucru sunt fixate la 8 ore în fiecare zi, iar pentru a calcula orele de lucru între două date prevăzute în celula A16 și B16, vă rugăm să utilizați formula astfel:
=NETWORKDAYS(A16,B16) * 8
Anunturi Intrați tasta și apoi formatați rezultatul ca general sau număr.
Pentru mai multe exemple despre calcularea orelor de lucru între două date, vă rugăm să vizitați Obțineți ore de lucru între două date în Excel
Dacă aveţi Kutools pentru Excel instalat în Excel, 90 de procente din calculele diferențelor date și oră pot fi rezolvate rapid fără a ne aminti nicio formulă.
1.31 Calculați diferența dintre două date și ore de către Data & Time Helper
Pentru a calcula diferența dintre două date și ore în Excel, doar Asistență pentru dată și oră E deajuns.
1. Selectați o celulă în care plasați rezultatul calculat și faceți clic Kutools > Formula Helper > Asistent pentru dată și oră.
2. În popping Asistență pentru dată și oră dialog, urmați setările de mai jos:
- Verifica Diferenţă opțiune;
- Selectați data de început și ora de sfârșit în Argumente de intrare de asemenea, puteți introduce manual data și ora în caseta de introducere sau faceți clic pe pictograma calendar pentru a selecta data;
- Selectați tipul de rezultat de ieșire din lista derulantă;
- Previzualizează rezultatul în Rezultat secţiune.
3. clic Ok. Rezultatul calculat este afișat și trageți mânerul de completare automată peste celulele pe care trebuie să le calculați.
Sfat:
Dacă doriți să obțineți diferența dintre două date și să afișați rezultatul ca zile, ore și minute cu Kutools pentru Excel, vă rugăm să procedați după cum urmează:
Selectați o celulă în care doriți să plasați rezultatul și faceți clic Kutools > Formula Helper > Data și ora > Numără zile, ore și minute între două date.
Apoi în Ajutor pentru formule dialog, specificați data de început și data de încheiere, apoi faceți clic Ok.
Iar rezultatul diferenței va fi afișat ca zile, ore și minute.
Clic Asistență pentru dată și oră pentru a afla mai multe despre utilizarea acestei funcții.
Clic Kutools pentru Excel pentru a cunoaște toate caracteristicile acestui program de completare.
Clic Descărcare gratuită pentru a obține o încercare gratuită de 30 de zile a Kutools pentru Excel
Dacă doriți să numărați rapid weekendul, zilele lucrătoare sau o anumită zi a săptămânii între două date, Kutools pentru Excel Formula Helper grupul te poate ajuta.
1. Selectați celula care va plasa rezultatul calculat, faceți clic Kutools > Statistic > Numărul de zile nelucrătoare între două date/Numărul de zile lucrătoare între două date/Numărați numărul de anumite zile ale săptămânii.
2. În popping out Ajutor pentru formule dialog, specificați data de început și data de încheiere, dacă aplicați Numărați numărul anumitor zile ale săptămânii, trebuie să specificați și ziua săptămânii.
Pentru a număra ziua specifică a săptămânii, puteți consulta nota pentru a utiliza 1-7 pentru a indica duminica-sâmbătă.
3. clic Ok, apoi trageți mânerul de completare automată peste celulele care trebuie să conteze numărul de weekend/zi lucrătoare/zi specifică, dacă este necesar.
Clic Kutools pentru Excel pentru a cunoaște toate caracteristicile acestui program de completare.
Clic Descărcare gratuită pentru a obține o încercare gratuită de 30 de zile a Kutools pentru Excel
2. Adăugați sau scădeți data și ora
Cu excepția calculului diferenței dintre două date, adunarea sau scăderea este, de asemenea, calculul normal de date și oră în Excel. De exemplu, poate doriți să obțineți data scadentă pe baza datei de producție și a numărului de zile de conservare pentru un produs.
2.11 Adăugați sau scădeți zile la o dată
Pentru a adăuga sau scădea un anumit număr de zile la o dată, iată două metode diferite.
Presupunând că adăugați 21 de zile la o dată în celula A2, alegeți una dintre metodele de mai jos pentru a o rezolva,
Metoda 1 data+zile
Selectați o celulă și introduceți formula:
=A+21
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă doriți să scădeți 21 de zile, se schimbă semnul plus (+) în semnul minus (-).
Metoda 2 Paste Speciale
1. Introduceți numărul de zile pe care doriți să le adăugați într-o celulă presupunând în celula C2, apoi apăsați Ctrl + C pentru ao copia.
2. Apoi selectați datele pe care doriți să le adăugați 21 de zile, faceți clic dreapta pentru a afișa meniul contextual și selectați Lipire specială....
3. În Lipiți special dialog, verificați Adăuga opțiunea (Dacă doriți să scădeți zilele, verificați Scădea opțiune). Clic OK.
4. Acum datele originale se schimbă în numere din 5 cifre, formatați-le ca date.
2.12 Adăugați sau scădeți luni la o dată
Pentru a adăuga sau scădea luni la o dată, se poate folosi funcția EDATE.
Clic EDATAȚI pentru a studia argumentele și utilizarea acestuia.
Presupunând că adăugați 6 luni la data din celula A2, utilizați formula astfel:
=EDATE(A2,6)
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă doriți să scădeți 6 luni de la dată, schimbați 6 cu -6.
2.13 Adăugați sau scădeți ani la o dată
Pentru a adăuga sau scădea n ani la o dată, se poate folosi o formulă care combină funcțiile DATĂ, AN, LUNA și ZI.
Presupunând că adăugați 3 ani la data din celula A2, utilizați formula astfel:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă doriți să scădeți 3 ani de la dată, schimbați 3 cu -3.
2.14 Adăugați sau scădeți săptămâni la o dată
Pentru a adăuga sau scădea săptămâni la o dată, formula generală este
Presupunând că adăugați 4 săptămâni la data din celula A2, utilizați formula astfel:
=A2+4*7
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă doriți să scădeți 4 săptămâni de la dată, schimbați semnul plus (+) în semnul minus (-).
2.15 Adăugați sau scădeți zile lucrătoare, inclusiv sau excluzând sărbătorile
În această secțiune, prezintă modul de utilizare a funcției WORKDAY pentru a adăuga sau scădea zile lucrătoare la o dată dată, excluzând sărbătorile sau inclusiv sărbătorii.
Vizita ZI DE LUCRU pentru a afla mai multe detalii despre argumentele și utilizarea acestuia.
Adăugați zile lucrătoare, inclusiv sărbători
În celula A2 este data pe care o utilizați, în celula B2 conține numărul de zile pe care doriți să le adăugați, vă rugăm să utilizați formula astfel:
=WORKDAY(A2,B2)
Anunturi Intrați cheie pentru a obține rezultatul.
Adăugați zile lucrătoare, cu excepția sărbătorilor
În celula A5 este data pe care o utilizați, în celula B5 conține numărul de zile pe care doriți să le adăugați, în intervalul D5:D8 listează sărbătorile, vă rugăm să utilizați formula astfel:
=WORKDAY(A5,B5,D5:D8)
Anunturi Intrați cheie pentru a obține rezultatul.
Notă:
Funcția WORKDAY ia sâmbăta și duminica ca weekend-uri, dacă weekendurile dvs. sunt sâmbătă și duminică, puteți aplica funcția WOKRDAY.INTL, care acceptă specificarea weekend-urilor.
Vizita WORKDAY.INTL pentru mai multe detalii.
Dacă doriți să scădeți zilele lucrătoare la o dată, trebuie doar să schimbați numărul de zile în negativ în formulă.
2.16 Adăugați sau scădeți anumiți an, lună, zile la o dată
Dacă doriți să adăugați un anumit an, lună zile la o dată, formula care combină funcția DATE, YEAR, MONTH și DAYS vă poate face o favoare.
Pentru a adăuga 1 an, 2 luni și 30 de zile la o dată din A11, vă rugăm să utilizați formula astfel:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă doriți să scădeți, schimbați toate semnele plus (+) în semnele minus (-).
2.21 Adăugați sau scădeți ore/minute/secunde la o dată și oră
Aici oferă câteva formule pentru a adăuga sau scădea ore, minute sau secunde la o dată și oră.
Adăugați sau scădeți ore la o dată și oră
Presupunând că adăugați 3 ore la o dată și oră (de asemenea, poate fi o oră) în celula A2, vă rugăm să utilizați formula astfel:
=A2+3/24
Anunturi Intrați cheie pentru a obține rezultatul.
Adăugați sau scădeți ore la o dată și oră
Presupunând că adăugați 15 minute la o dată și oră (de asemenea, poate fi o oră) în celula A5, vă rugăm să utilizați formula astfel:
=A2+15/1440
Anunturi Intrați cheie pentru a obține rezultatul.
Adăugați sau scădeți ore la o dată și oră
Presupunând că adăugați 20 de secunde la o dată și oră (de asemenea, poate fi o oră) în celula A8, vă rugăm să utilizați formula astfel:
=A2+20/86400
Anunturi Intrați cheie pentru a obține rezultatul.
2.22 Suma timpilor peste 24 de ore
Presupunând că există un tabel Excel care înregistrează timpul de lucru al tuturor personalului într-o săptămână, pentru a însuma timpul total de lucru pentru calcularea plăților, puteți utiliza SUM(interval) pentru a obține rezultatul. Dar, în general, rezultatul însumat va fi afișat ca un timp care nu depășește 24 de ore, așa cum arată captura de ecran de mai jos, cum puteți obține rezultatul corect?
De fapt, trebuie doar să formatați rezultatul ca [hh]:mm:ss.
Faceți clic dreapta pe celula rezultat, alegeți Celule de format în meniul contextual și în popping Celule de format dialog, alegeți pachet personalizat din blist și tastați [hh]:mm:ss în caseta text din secțiunea din dreapta, faceți clic OK.
Rezultatul însumat va fi afișat corect.
2.23 Adăugați programul de lucru la o dată, excluzând weekend-ul și vacanța
Aici oferă o formulă lungă pentru obținerea datei de încheiere bazată pe adăugarea unui anumit număr de ore de lucru la o dată de începere și exclude weekendurile (sâmbătă și duminică) și sărbătorile.
Într-un tabel Excel, A11 conține data de început și ora, iar B11 conține orele de lucru, în celula E11 și E13 sunt orele de începere și de sfârșit de lucru, iar celula E15 conține vacanța care va fi exclusă.
Vă rugăm să utilizați formula astfel:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Anunturi Intrați cheie pentru a obține rezultatul.
Dacă aveți instalat Kutools pentru Excel, un singur instrument - Data și ora Helper poate rezolva majoritatea calculelor privind adunarea și scăderea datetime.
1. Faceți clic pe o celulă pentru care doriți să scoateți rezultatul și aplicați acest instrument făcând clic Kutools > Formula Helper > Data & Time Helper.
2. În Asistență pentru dată și oră dialog, verificați Adăuga opțiune sau Scădea opțiunea după cum aveți nevoie, apoi alegeți celula sau introduceți direct data și ora în care doriți să o utilizați Argumente de intrare secțiunea, apoi specificați ani, luni, săptămâni, zile, ore, minute și secunde pe care doriți să le adăugați sau să scădeți, apoi faceți clic Ok. Vedeți captura de ecran:
Puteți previzualiza rezultatul calculat în Rezultat secţiune.
Acum rezultatul este afișat, trageți mânerul automat peste alte celule pentru a obține rezultatele.
Clic Asistență pentru dată și oră pentru a afla mai multe despre utilizarea acestei funcții.
Clic Kutools pentru Excel pentru a cunoaște toate caracteristicile acestui program de completare.
Clic Descărcare taxă pentru a obține o încercare gratuită de 30 de zile a Kutools pentru Excel
2.41 Verificați sau evidențiați dacă o dată este expirată
Dacă există o listă cu datele expirate ale produselor, vă recomandăm să verificați și să evidențiați datele care au expirat pe baza zilei de astăzi, așa cum se arată în captura de ecran de mai jos.
De fapt, Formatarea condițională se poate descurca rapid cu această slujbă.
1. Selectați datele pe care doriți să le verificați, apoi faceți clic Acasă > Formatarea condițională > Noua regulă.
2. În Noua regulă de formatare , selectați Utilizați o formulă pentru a determina ce celule să formatați în Selectați un tip de regulă secțiune și tip =B2 în caseta de introducere (B2 este prima dată pe care doriți să o verificați) și faceți clic Format să apară Celule de format dialog, apoi alegeți o formatare diferită pentru a depăși datele expirate după cum aveți nevoie. Clic OK > OK.
2.42 Returnează sfârșitul lunii curente/prima zi a lunii următoare/a>
Datele expirate ale unor produse sunt la sfârșitul lunii de producție sau în prima zi a lunii următoare de producție, pentru a enumera rapid datele expirate pe baza datei de producție, vă rugăm să urmați această parte.
Obțineți sfârșitul lunii curente
Iată o dată de producție în celula B13, vă rugăm să utilizați formula astfel:
=EOMONTH(B13,0)
Anunturi Intrați cheie pentru a obține rezultatul.
Obțineți prima zi a lunii viitoare
Iată o dată de producție în celula B18, vă rugăm să utilizați formula astfel:
=EOMONTH(B18,0)+1
Anunturi Intrați cheie pentru a obține rezultatul.
3. Calculați vârsta
În această secțiune, listează metodele de rezolvare a modului de calcul al vârstei pe baza unei date date sau a unui număr de serie.
3.11 Calculați vârsta pe baza datei de naștere date
Obțineți vârsta în număr zecimal în funcție de data nașterii
Clic ANUL FRAC pentru detalii despre argumentele sale și despre utilizare.
De exemplu, pentru a obține vârstele pe baza listei de date de naștere din coloana B2:B9, vă rugăm să utilizați formula astfel:
=YEARFRAC(B2,TODAY())
Anunturi Intrați tasta, apoi trageți în jos mânerul de completare automată până când sunt calculate toate vârstele.
Sfat:
1) Puteți specifica zecimala după cum aveți nevoie în Celule de format dialog.
2) Dacă doriți să calculați vârsta la o anumită dată pe baza unei date de naștere date, schimbați TODAY() la data specifică inclusă cu ghilimele duble, cum ar fi =YEARFRAC(B2,"1/1/2021")
3) Dacă doriți să obțineți vârsta anului următor pe baza datei de naștere, adăugați doar 1 în formula, cum ar fi =YEARFRAC(B2,TODAY())+1.
Obțineți vârsta în număr întreg în funcție de data nașterii
Clic DATEDIF pentru detalii despre argumentele sale și despre utilizare.
Folosind exemplul de mai sus, pentru a obține vârsta pe baza datelor de naștere din lista din B2:B9, vă rugăm să utilizați formula astfel:
=DATEDIF(B2,TODAY(),"y")
Anunturi Intrați tasta, apoi trageți în jos mânerul de completare automată până când sunt calculate toate vârstele.
Sfat:
1) Dacă doriți să calculați vârsta la o anumită dată pe baza unei date de naștere date, schimbați TODAY() la data specifică inclusă cu ghilimele duble, cum ar fi =DATEDIF(B2,"1/1/2021","y") .
2) Dacă doriți să obțineți vârsta anului următor pe baza datei de naștere, adăugați doar 1 în formula, cum ar fi =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calculați vârsta în format de ani, lună și zile în funcție de ziua de naștere dată
Dacă doriți să calculați vârsta pe baza unei date de naștere date și să afișați rezultatul ca xx ani, xx luni, xx zile, așa cum arată captura de ecran de mai jos, iată o formulă lungă care vă poate ajuta.
Pentru a obține vârsta în ani, luni și zile pe baza datei de naștere din celula B12, vă rugăm să utilizați formula astfel:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Anunturi Intrați pentru a obține vârsta, apoi trageți mânerul de completare automată în jos în alte celule.
Sfat:
Dacă doriți să calculați vârsta într-o anumită dată pe baza unei date de naștere date, schimbați TODAY() la data specifică inclusă cu ghilimele duble, cum ar fi = =DATEDIF(B12,"1/1/2021","Y")& " Ani, "&DATEDIF(B12,"1/1/2021","YM")&" Luni, "&DATEDIF(B12,"1/1/2021","MD")&" Zile".
3.13 Calculați vârsta după data nașterii înainte de 1/1/1900
În Excel, data de dinainte de 1/1/1900 nu poate fi introdusă ca dată oră sau calculată corect. Dar dacă vrei să calculezi vârsta unei persoane celebre pe baza datei de naștere (înainte de 1/11900) și a morții, doar un cod VBA te poate ajuta.
1. presa Alt + F11 taste pentru activare Microsoft Visual Basic pentru aplicații fereastră și faceți clic pe Insera filă și alegeți Module pentru a crea un modul nou.
2. Apoi copiați și inserați codul de mai jos în noul modul.
VBA: calculați vârsta înainte de 1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Salvați codul și reveniți la foaie și selectați o celulă pentru a plasa vârsta calculată, tastați =AgeFunc(birthdate,dethdate), în acest caz, =AgeFunc(B22,C22), apăsați tasta Enter pentru a obține vârsta. Și utilizați mânerul de umplere automată pentru a aplica această formulă altor celule, dacă este necesar.
Dacă aveţi Kutools pentru Excel instalat în Excel, puteți aplica Asistență pentru dată și oră instrument pentru a calcula vârsta.
1. Selectați o celulă în care doriți să plasați vârsta calculată și faceți clic Kutools > Formula Helper > Asistent pentru dată și oră.
2. În Asistență pentru dată și oră dialog,
- 1) Verificați Vârstă opțiune;
- 2) Alegeți celula datei nașterii sau introduceți direct data nașterii sau faceți clic pe pictograma calendar pentru a selecta data nașterii;
- 3) Alegeți Astăzi opțiunea dacă doriți să calculați vârsta curentă, alegeți Data specificată opțiunea și introduceți data dacă doriți să calculați vârsta în trecut sau viitor;
- 4) Specificați tipul de ieșire din lista derulantă;
- 5) Previzualizați rezultatul rezultatului. Clic Ok.
Clic Asistență pentru dată și oră pentru a afla mai multe despre utilizarea acestei funcții.
Clic Kutools pentru Excel pentru a cunoaște toate caracteristicile acestui program de completare.
Clic Descărcare gratuită pentru a obține o încercare gratuită de 30 de zile a Kutools pentru Excel
3.31 Obțineți ziua de naștere de la numărul de identificare
Dacă există o listă de numere de identificare care utilizează primele 6 cifre pentru a înregistra data nașterii, cum ar fi 920315330, înseamnă că data nașterii este 03/15/1992, cum puteți introduce rapid data nașterii într-o altă coloană?
Acum să luăm ca exemplu lista numerelor de identificare care încep în celula C2 și să folosim formula astfel:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Anunturi Intrați cheie. Apoi trageți mânerul de completare automată în jos pentru a obține alte rezultate.
Notă:
În formulă, puteți modifica referința la nevoia dvs. De exemplu, dacă numărul ID afișat ca 13219920420392, ziua de naștere este 04/20/1992, puteți schimba formula în =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ „&MID(C2,4,4) pentru a obține rezultatul corect.
3.32 Calculați vârsta din numărul de identificare
Dacă există o listă de numere de identificare care utilizează primele 6 cifre pentru a înregistra data nașterii, cum ar fi 920315330, înseamnă că data nașterii este 03/15/1992, cum puteți calcula rapid vârsta pe baza fiecărui număr de identificare în Excel?
Acum să luăm ca exemplu lista numerelor de identificare care încep în celula C2 și să folosim formula astfel:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
Anunturi Intrați cheie. Apoi trageți mânerul de completare automată în jos pentru a obține alte rezultate.
Notă:
În această formulă, dacă anul este mai mic decât anul curent, anul va fi considerat ca începând cu 20, cum ar fi 200203943 va fi considerat anul 2020; dacă anul este mai mare decât anul curent, anul va fi considerat ca începând cu 19, cum ar fi 920420392 va fi considerat anul 1992.
Mai multe tutoriale Excel:
Combinați mai multe cărți de lucru/fișe de lucru într-una singură
Acest tutorial, enumerând aproape toate scenariile de combinare cu care vă puteți confrunta și oferind soluții profesionale relative pentru dvs.
Divizarea celulelor text, număr și dată (separați în mai multe coloane)
Acest tutorial este împărțit în trei părți: divizarea celulelor de text, împărțirea celulelor cu numere și împărțirea celulelor date. Fiecare parte oferă exemple diferite pentru a vă ajuta să știți cum să gestionați lucrarea de împărțire atunci când întâmpinați aceeași problemă.
Combinați conținutul mai multor celule fără a pierde date în Excel
Acest tutorial restrânge extragerea la o anumită poziție într-o celulă și colectează diferite metode pentru a ajuta la extragerea textului sau a numerelor dintr-o celulă după o anumită poziție în Excel.
Comparați două coloane pentru potriviri și diferențe în Excel
Aici, acest articol acoperă cele mai posibile scenarii de comparare a două coloane pe care le-ați putea întâlni și sper că vă poate ajuta.
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%
- Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
- Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
- Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
- Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
- Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
- Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
- Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
- Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
- Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
