Vadnica za Excel: Izračun datuma in časa (izračunajte razliko, starost, seštejte/odštejte)

V Excelu se pogosto uporablja izračun datuma in časa, kot je izračun razlike med dvema datumoma/časoma, seštevanje ali odštevanje datuma in časa, pridobivanje starosti na podlagi datuma rojstva in tako naprej. Tukaj, v tej vadnici, navaja skoraj scenarije o izračunu datuma in časa in nudi povezane metode za vas.

Krmarjenje po tej vadnici

1. Izračunajte razliko med dvema datumoma/časoma 1.1 Izračunajte razliko dan/mesec/leto med dvema datumoma 1.11 Izračunaj razliko med dvema datumoma v dnevih/meseci/letih/tednih 1.12 Izračunajte mesece in ne upoštevajte let in dni med dvema datumoma 1.13 Izračunaj dneve in ne upošteva let in mesecev med dvema datumoma 1.14 Izračunajte razliko med dvema datumoma in vrnite leta, mesece in dneve 1.15 Izračunaj razliko med datumom in današnjim dnem 1.16 Izračunajte delovne dni z ali brez praznikov med dvema datumoma 1.17 Izračunaj vikende med dvema datumoma 1.18 Izračunajte določen dan v tednu med dvema datumoma 1.19 Izračunajte preostale dni v mesecu/letu

1.2 Izračunaj razliko med dvema časoma 1.21 Izračunaj razliko med dvema časoma 1.22 Izračunajte razliko med dvema časoma v urah/ minutah/sekundah 1.23 Izračunaj razliko v urah samo med dvema časoma (ne presega 24 ur) 1.24 Izračunaj minutno razliko samo med dvema časoma (ne presega 60 minut) 1.25 Izračunaj sekundno razliko samo med dvema časoma (ne presega 60 sekund) 1.26 Izračunajte razliko med dvema časoma in povratnimi urami, minutami, sekundami 1.27 Izračunaj razliko med dvema datumoma 1.28 Izračunaj časovno razliko z milisekundami 1.29 Izračunajte delovni čas med dvema datumoma brez vikendov

1.3 Izračunajte razliko med dvema datumoma in časom s Kutools for Excel 1.31 Izračunajte razliko med dvema datumoma in časom s pomočjo Data & Time Helper 1.32 Izračunajte razliko vikend/delovnik/določen dan v tednu med dvema datumoma s pomočjo Formula Helper

1.4 Združite dva stolpca, če sta prazna 1.41 Uporaba funkcije IF 1.42 Uporaba VBA

2. Dodajte ali odštejte datum in uro 2.1 Datumu dodajte ali odštejte dneve/mesece/leta/tedne/delovnike 2.11 Dodajanje ali odštevanje dni od datuma 2.12 Dodajanje ali odštevanje mesecev od datuma 2.13 Dodajanje ali odštevanje let datumu 2.14 Dodajte ali odštejte tedne k datumu 2.15 Seštejte ali odštejte delovne dni, vključno s prazniki ali brez njih 2.16 Dodajte ali odštejte določeno leto, mesec, dneve od datuma

2.2 Dodajte ali odštejte ure/minute/sekunde časom 2.21 Datumu in času dodajte ali odštejte ure/minute/sekunde 2.22 Seštevek časov v 24 urah 2.23 Datumu dodajte delovni čas brez vikenda in praznika

2.3 Dodajte ali odštejte datum/čas s Kutools za Excel

2.4 Podaljšanje 2.41 Preverite ali označite, če je datum potekel 2.42 Vrni konec tekočega meseca/prvi dan naslednjega meseca

3. Izračunajte starost 3.1 Izračunajte starost glede na datum 3.11 Izračunajte starost glede na datum rojstva 3.12 Izračunajte starost v obliki let, mesecev in dni glede na datum rojstva 3.13 Izračunajte starost glede na datum rojstva pred 1. 1. 1900

3.2 Izračunajte starost ob rojstvu z uporabo Kutools for Excel

3.3 Izračunajte starost ali pridobite datum rojstva na podlagi številke serije 3.31 Pridobite rojstni dan iz ID številke 3.32 Izračunajte starost iz ID številke

 

V tej vadnici ustvarim nekaj primerov za razlago metod, lahko spremenite reference, ki jih potrebujete, ko uporabite spodnjo kodo VBA ali formule

---

1. Izračunajte razliko med dvema datumoma/časoma

Izračun razlike med dvema datumoma ali dvema časoma je lahko najbolj običajna težava izračuna datuma in časa, s katero se srečujete pri vsakodnevnem delu v Excelu. Spodnji primeri vam lahko pomagajo povečati učinkovitost, ko naletite na iste težave.

1.11 Izračunaj razliko med dvema datumoma v dnevih/meseci/letih/tednih

Excelovo funkcijo DATEDIF lahko uporabite za hiter izračun razlike med dvema datumoma v dnevih, mesecih, letih in tednih.

Kliknite za več podrobnosti o PODATKOV funkcija

Dnevi razlike med dvema datumoma

Če želite dobiti razliko v dneh med dvema datumoma v celici A2 in B2, uporabite naslednjo formulo

=DATEDIF(A2,B2,"d")

Pritisnite Vnesite ključ, da dobite rezultat.

Mesečna razlika med dvema datumoma

Če želite dobiti mesečno razliko med dvema datumoma v celici A5 in B5, uporabite naslednjo formulo

=DATEDIF(A5,B5,"m")

Pritisnite Vnesite ključ, da dobite rezultat.

Letna razlika med dvema datumoma

Če želite dobiti razliko v letih med dvema datumoma v celici A8 in B8, uporabite naslednjo formulo

=DATEDIF(A8,B8,"y")

Pritisnite Vnesite ključ, da dobite rezultat.

Tedenska razlika med dvema datumoma

Če želite dobiti razliko tednov med dvema datumoma v celici A11 in B11, uporabite naslednjo formulo

=DATEDIF(A11,B11,"d")/7

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba:

1) Ko uporabite zgornjo formulo za pridobitev tedne razlike, lahko vrne rezultat v datumski obliki, rezultat morate oblikovati v splošno ali številko, kot želite.

2) Ko uporabite zgornjo formulo, da dobite razliko tednov, se lahko vrne na decimalno število, če želite dobiti celo številko tedna, lahko pred tem dodate funkcijo ROUNDDOWN, kot je prikazano spodaj, da dobite razliko celega tedna:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1.12 Izračunajte mesece in ne upoštevajte let in dni med dvema datumoma

Če želite le izračunati razliko v mesecih, ne da bi upoštevali leta in dneve med dvema datumoma, kot prikazuje spodnji posnetek zaslona, ​​je tu formula, ki vam lahko pomaga.

=DATEDIF(A2,B2,"ym")

Pritisnite Vnesite ključ, da dobite rezultat.

A2 je začetni datum, B2 pa končni datum.

1.13 Izračunaj dneve in ne upošteva let in mesecev med dvema datumoma

Če želite samo izračunati razliko v dnevih, ne da bi upoštevali leta in mesece med dvema datumoma, kot je prikazano na spodnjem posnetku zaslona, ​​je tukaj formula, ki vam lahko pomaga.

=DATEDIF(A5,B5,"md")

Pritisnite Vnesite ključ, da dobite rezultat.

A5 je začetni datum, B5 pa končni datum.

1.14 Izračunajte razliko med dvema datumoma in vrnite leta, mesece in dneve

Če želite pridobiti razliko med dvema datumoma in vrniti xx let, xx mesecev in xx dni, kot prikazuje spodnji posnetek zaslona, ​​je tukaj na voljo tudi formula.

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

Pritisnite Vnesite ključ, da dobite rezultat.

A8 je začetni datum, B8 pa končni datum.

1.15 Izračunaj razliko med datumom in današnjim dnem

Če želite samodejno izračunati razliko med datumom in današnjim dnem, samo spremenite end_date v zgornjih formulah v TODAY(). Tukaj je primer za izračun dnevne razlike med preteklim datumom in današnjim dnem.

=DATEDIF(A11,TODAY(),"d")

Pritisnite Vnesite ključ, da dobite rezultat.

Opombe: če želite izračunati razliko med prihodnjim datumom in današnjim dnem, spremenite začetni_datum na danes in vzemite prihodnji datum kot končni_datum takole:

=DATEDIF(TODAY(),A14,"d")

Upoštevajte, da mora biti začetni_datum manjši od končnega_datuma v funkciji DATEDIF, sicer se vrne na #NUM! vrednost napake.

1.16 Izračunajte delovne dni z ali brez praznikov med dvema datumoma

Včasih boste morda morali prešteti število delovnih dni z ali brez praznikov med dvema podanima datumoma.

V tem delu boste uporabili funkcijo NETWORKDAYS.INTL:

klik NETWORKDAYS.INTL poznati njegove argumente in uporabo.

Štejte delavnike s prazniki

Za štetje delovnih dni s prazniki med dvema datumoma v celici A2 in B2 uporabite naslednjo formulo:

=NETWORKDAYS.INTL(A2,B2)

Pritisnite Vnesite ključ, da dobite rezultat.

Štejte delavnike brez praznikov

Če želite prešteti delovne dni s prazniki med dvema datumoma v celici A2 in B2 ter izključiti praznike v obsegu D5:D9, uporabite naslednjo formulo:

=NETWORKDAYS.INTL(A5;B5,1;5;D9:DXNUMX)

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba:

V zgornjih formulah za vikend vzameta soboto in nedeljo. Če imate drugačne dneve vikenda, spremenite argument [vikend], kot želite.

1.17 Izračunaj vikende med dvema datumoma

Če želite prešteti število vikendov med dvema datumoma, vam lahko uslugo storita funkciji SUMPRODUCT ali SUM.

Če želite prešteti vikende (sobota in nedelja) med dvema datumoma v celici A12 in B12:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))

Or

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

Pritisnite Vnesite ključ, da dobite rezultat.

1.18 Izračunajte določen dan v tednu med dvema datumoma

Za štetje števila določenih delovnih dni, kot je ponedeljek med dvema datumoma, vam lahko pomaga kombinacija funkcij INT in WEEKDAY.

Celica A15 in B15 sta dva datuma, med katerima želite šteti ponedeljek, uporabite naslednjo formulo:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

Pritisnite Vnesite ključ, da dobite rezultat.

Spremenite številko dneva v tednu v funkciji WEEKDAY, da se šteje drug dan v tednu:

1 je nedelja, 2 je ponedeljek, 3 je torek, 4 je sreda, 5 je četrtek, 6 je petek in 7 je sobota)

1.19 Izračunajte preostale dni v mesecu/letu

Včasih boste morda želeli izvedeti preostale dni v mesecu ali letu glede na navedeni datum, kot prikazuje spodnji posnetek zaslona:

Pridobite preostale dni v tekočem mesecu

klik EOMESEC poznati argument in uporabo.

Za pridobitev preostalih dni tekočega meseca v celici A2 uporabite naslednjo formulo:

=EOMONTH(A2,0)-A2

Pritisnite Vnesite in povlecite ročico za samodejno izpolnjevanje, da po potrebi uporabite to formulo v drugih celicah.

Nasvet: rezultati so lahko prikazani kot oblika datuma, le spremenite jih v splošno ali številsko obliko.

Pridobite preostale dni v tekočem letu

Za pridobitev preostalih dni tekočega leta v celici A2 uporabite naslednjo formulo:

=DATE(YEAR(A2),12,31)-A2

Pritisnite Vnesite in povlecite ročico za samodejno izpolnjevanje, da po potrebi uporabite to formulo v drugih celicah.

---

1.21 Izračunaj razliko med dvema časoma

Če želite dobiti razliko med dvema časoma, sta vam lahko v pomoč dve preprosti formuli.

Recimo, da celici A2 in B2 vsebujeta začetni_čas in končni_čas ločeno, z uporabo naslednjih formul:

=B2-A2

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

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba:

• Če uporabite end_time-start_time, lahko rezultat oblikujete v drugo časovno obliko, kot jo potrebujete, v pogovornem oknu Oblikuj celice.
• Če uporabljate TEXT(end_time-first_time,"time_format"), vnesite časovno obliko, za katero želite, da je rezultat prikazan v formuli, na primer TEXT(end_time-first_time,"h") vrne 16.
• Če je končni_čas manjši od začetnega_časa, obe formuli vrneta vrednosti napak. Za rešitev te težave lahko dodate ABS pred temi formulami, na primer ABS(B2-A2), ABS(BESEDILO(B2-A2,"hh:mm:ss")), nato rezultat oblikujete kot čas.

1.22 Izračunajte razliko med dvema časoma v urah/ minutah/sekundah

Če želite izračunati razliko med dvema časoma v urah, minutah ali sekundah, kot prikazuje spodnji posnetek zaslona, ​​upoštevajte ta del.

Dobite urne razlike med dvema časoma

Če želite dobiti razliko v urah med dvema časoma v A5 in B5, uporabite naslednjo formulo:

=INT((B5-A5)*24)

Pritisnite Vnesite ključ, nato formatirajte rezultat oblike časa kot splošno ali številko.

Če želite dobiti razliko v decimalnih urah, uporabite (end_time-start_time)*24.

Dobite minutno razliko med dvema časoma

Če želite dobiti minutno razliko med dvema časoma v A8 in B8, uporabite naslednjo formulo:

=INT((B8-A8)*1440)

Pritisnite Vnesite ključ, nato formatirajte rezultat oblike časa kot splošno ali številko.

Če želite dobiti razliko v decimalnih minutah, uporabite (end_time-start_time)*1440.

Dobite sekundno razliko med dvema časoma

Če želite dobiti sekundno razliko med dvema časoma v A5 in B5, uporabite naslednjo formulo:

=(B11-A11)*86400)

Pritisnite Vnesite ključ, nato formatirajte rezultat oblike časa kot splošno ali številko.

1.23 Izračunaj razliko v urah samo med dvema časoma (ne presega 24 ur)

Če razlika med dvema časoma ne presega 24 ur, lahko funkcija HOUR hitro pridobi urno razliko med tema časoma.

klik HOUR za več podrobnosti o tej funkciji.

Če želite dobiti razliko v urah med časoma v celici A14 in B14, uporabite funkcijo HOUR na naslednji način:

=HOUR(B14-A14)

Pritisnite Vnesite ključ, da dobite rezultat.

Začetni_čas mora biti manjši od končnega_časa, sicer formula vrne #ŠTEV! vrednost napake.

1.24 Izračunaj minutno razliko samo med dvema časoma (ne presega 60 minut)

Funkcija MINUTE lahko hitro dobi razliko v minutah med tema časoma in prezre ure in sekunde.

klik MINUTE za več podrobnosti o tej funkciji.

Če želite dobiti samo minutno razliko med časoma v celici A17 in B17, uporabite funkcijo MINUTE, kot sledi:

=MINUTE(B17-A17)

Pritisnite Vnesite ključ, da dobite rezultat.

Začetni_čas mora biti manjši od končnega_časa, sicer formula vrne #ŠTEV! vrednost napake.

1.25 Izračunaj sekundno razliko samo med dvema časoma (ne presega 60 sekund)

Funkcija SECOND lahko hitro dobi edino sekundno razliko med tema časoma in prezre ure in minute.

klik DRUGA za več podrobnosti o tej funkciji.

Če želite dobiti samo razliko v sekundah med časoma v celici A20 in B20, uporabite funkcijo SECOND kot je ta:

=SECOND(B20-A20)

Pritisnite Vnesite ključ, da dobite rezultat.

Začetni_čas mora biti manjši od končnega_časa, sicer formula vrne #ŠTEV! vrednost napake.

1.26 Izračunajte razliko med dvema časoma in povratnimi urami, minutami, sekundami

Če želite prikazati razliko med dvema časoma kot xx ur xx minut xx sekund, uporabite funkcijo TEXT, kot je prikazano spodaj:

klik BESEDILO spoznati argumente in uporabo te funkcije.

Za izračun razlike med časoma v celici A23 in B23 uporabite naslednjo formulo:

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba:

Ta formula prav tako izračuna samo razliko v urah, ki ne presega 24 ur, končni_čas pa mora biti večji od začetnega_časa, sicer vrne #VREDNOST! vrednost napake.

1.27 Izračunaj razliko med dvema datumoma

Če sta v formatu mm/dd/llll hh:mm:ss dva časa, lahko za izračun razlike med njima uporabite eno od spodnjih formul, kot želite.

Pridobite časovno razliko med dvema datumoma in vrnite rezultat v obliki hh:mm:ss

Vzemite dva datuma in časa v celici A2 in B2 kot primer, uporabite formulo kot je ta:

=B2-A2

Pritisnite Vnesite ključ, ki vrne rezultat v obliki datuma in časa, nato ta rezultat formatirajte kot [h]: mm: ss v kategoriji po meri pod Število zanka v Oblikuj celice dialog.
 

Pridobite razliko med dvema datumoma in vrnite dneve, ure, minute, sekunde

Vzemite dva datuma in časa v celici A5 in B5 kot primer, uporabite formulo kot je ta:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba: v obeh formulah mora biti končni_datum večji od začetnega_datuma, sicer formule vrnejo vrednosti napak.

1.28 Izračunaj časovno razliko z milisekundami

Najprej morate vedeti, kako formatirati celico, da prikaže milisekunde:

Izberite celice, ki jih želite prikazati v milisekundah, in desno izberite Oblikuj celice da omogočite Oblikuj celice dialog, izberite po meri v Kategorija pod zavihkom Število in vnesite to hh: mm: ss.000 v besedilno polje.

Uporabite formulo:

Tukaj za izračun razlike med dvema časoma v celici A8 in B8 uporabite formulo kot:

=ABS(B8-A8)

Pritisnite Vnesite ključ, da dobite rezultat.

1.29 Izračunajte delovni čas med dvema datumoma brez vikendov

Včasih boste morda morali šteti delovne ure med dvema datumoma, razen ob koncu tedna (sobota in nedelja).

Tu je delovni čas določen na 8 ur vsak dan in za izračun delovnega časa med dvema datumoma v celici A16 in B16 uporabite naslednjo formulo:

=NETWORKDAYS(A16,B16) * 8

Pritisnite Vnesite in nato formatirajte rezultat kot splošno ali številko.

Za več primerov o izračunavanju delovnega časa med dvema datumoma obiščite Poiščite delovne ure med dvema datumoma v Excelu

---

Če imate Kutools za Excel nameščen v Excelu, je mogoče hitro rešiti 90 odstotkov izračunov datumske in časovne razlike, ne da bi si zapomnili kakršne koli formule.

1.31 Izračunajte razliko med dvema datumoma in časom s pomočjo Data & Time Helper

Če želite izračunati razliko med dvema datumoma in časoma v Excelu, samo Pomočnik za datum in čas Je dovolj.

1. Izberite celico, v katero postavite rezultat izračuna, in kliknite Kutools > Pomočnik za formulo > Pomočnik za datum in čas.

2. V popping Pomočnik za datum in čas pogovornem oknu sledite spodnjim nastavitvam:

1. Preveri Razlika možnost;
2. Izberite začetni datum in uro v Vnos argumentov lahko tudi neposredno ročno vnesete datum in uro v polje za vnos ali kliknete ikono koledarja, da izberete datum;
3. Na spustnem seznamu izberite vrsto izhodnega rezultata;
4. Predogled rezultata v Rezultat oddelek.

3. klik Ok. Izračunan rezultat se prikaže in ročico za samodejno izpolnjevanje povlecite čez celice, ki jih morate prav tako izračunati.

Nasvet:

Če želite dobiti razliko med dvema datumoma in prikazati rezultat kot dneve, ure in minute s Kutools za Excel, naredite naslednje:

Izberite celico, kamor želite postaviti rezultat, in kliknite Kutools > Pomočnik za formulo > Datum čas > Štejte dneve, ure in minute med dvema datumoma.

Potem v Pomočnik za formule Določite začetni in končni datum ter kliknite Ok.

Rezultat razlike bo prikazan kot dnevi, ure in minute.

klik Pomočnik za datum in čas izvedeti več o uporabi te funkcije.

klik Kutools za Excel če želite poznati vse funkcije tega dodatka.

klik Brezplačen prenos da dobite 30-dnevno brezplačno preskusno različico Kutools za Excel

1.32 Izračunajte razliko vikend/delovnik/določen dan v tednu med dvema datumoma s pomočjo Formula Helper

Če želite hitro prešteti vikend, delovne dneve ali določen dan v tednu med dvema datumoma, Kutools za Excel Pomočnik za formulo skupina vam lahko pomaga.

1. Izberite celico, v katero se bo vpisal rezultat izračuna, kliknite Kutools > Statistični > Število delovnih dni med dvema datumoma/Število delovnih dni med dvema datumoma/Preštejte število določenih dni v tednu.

2. V popping out Pomočnik za formule podajte začetni in končni datum, če se prijavite Preštejte število določenih dni v tednu, morate določiti tudi dan v tednu.

Za štetje določenega dneva v tednu se lahko obrnete na opombo in uporabite 1-7 za označevanje nedelja-sobota.

3. klik Ok, nato pa ročico za samodejno izpolnjevanje povlecite čez celice, ki morajo po potrebi šteti število vikendov/delovnikov/določenega dneva v tednu.

klik Kutools za Excel če želite poznati vse funkcije tega dodatka.

klik Brezplačen prenos da dobite 30-dnevno brezplačno preskusno različico Kutools za Excel

---

2. Dodajte ali odštejte datum in uro

Razen za izračun razlike med dvema datumskima časoma je seštevanje ali odštevanje tudi običajni izračun datumskega časa v Excelu. Na primer, morda želite pridobiti rok na podlagi datuma izdelave in števila dni hrambe za izdelek.

2.11 Dodajanje ali odštevanje dni od datuma

Če želite datumu dodati ali odšteti določeno število dni, sta tu dva različna načina.

Predvidevamo, da datumu v celici A21 dodamo 2 dni, izberite eno od spodnjih metod za rešitev,

Metoda 1 datum + dnevi

Izberite celico in vnesite formulo:

=A+21

Pritisnite Vnesite ključ, da dobite rezultat.

Če želite odšteti 21 dni, se znak plus (+) spremeni v znak minus (-).

2. način Posebno lepljenje

1. V celico vnesite število dni, ki jih želite dodati, na primer v celico C2, in nato pritisnite Ctrl + C da ga kopirate.

2. Nato izberite datume, ki jim želite dodati 21 dni, z desno miškino tipko kliknite, da se prikaže kontekstni meni, in izberite Posebno lepljenje ....

3. V Ljubljani Posebno lepljenje pogovorno okno, preverite Dodaj možnost (Če želite odšteti dneve, označite Odštej možnost). Kliknite OK.

4. Zdaj se prvotni datumi spremenijo v 5-mestna števila, oblikujte jih kot datume.

2.12 Dodajanje ali odštevanje mesecev od datuma

Če želite datumu dodati ali odšteti mesece, lahko uporabite funkcijo EDATE.

klik UREDI preučiti njegove argumente in uporabo.

Predpostavimo, da datumu v celici A6 dodamo 2 mesecev, uporabimo naslednjo formulo:

=EDATE(A2,6)

Pritisnite Vnesite ključ, da dobite rezultat.

Če želite datumu odšteti 6 mesecev, spremenite 6 v -6.

2.13 Dodajanje ali odštevanje let datumu

Če želite datumu dodati ali odšteti n let, lahko uporabite formulo, ki združuje funkcije DATUM, LETO, MESEC in DAN.

Predpostavimo, da datumu v celici A3 dodamo 2 leta, uporabimo naslednjo formulo:

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

Pritisnite Vnesite ključ, da dobite rezultat.

Če želite datumu odšteti 3 leta, spremenite 3 v -3.

2.14 Dodajte ali odštejte tedne k datumu

Če želite datumu dodati ali odšteti tedne, je splošna formula

Predpostavimo, da datumu v celici A4 dodamo 2 tedne, uporabimo naslednjo formulo:

=A2+4*7

Pritisnite Vnesite ključ, da dobite rezultat.

Če želite datumu odšteti 4 tedne, spremenite znak plus (+) v znak minus (-).

2.15 Seštejte ali odštejte delovne dni, vključno s prazniki ali brez njih

V tem razdelku je predstavljeno, kako uporabiti funkcijo WORKDAY za dodajanje ali odštevanje delovnih dni danemu datumu brez praznikov ali vključno s prazniki.

obisk DELOVNI DAN izvedeti več podrobnosti o njegovih argumentih in uporabi.

Dodajte delovne dni, vključno s prazniki

V celici A2 je datum, ki ga uporabljate, v celici B2 pa je število dni, ki jih želite dodati, uporabite naslednjo formulo:

=WORKDAY(A2,B2)

Pritisnite Vnesite ključ, da dobite rezultat.

Dodajte delovne dni brez praznikov

V celici A5 je datum, ki ga uporabljate, v celici B5 je število dni, ki jih želite dodati, v obsegu D5:D8 so navedeni prazniki, uporabite naslednjo formulo:

=WORKDAY(A5,B5,D5:D8)

Pritisnite Vnesite ključ, da dobite rezultat.

Opomba:

Funkcija WORKDAY vzame soboto in nedeljo kot vikend; če sta vaša vikenda sobota in nedelja, lahko uporabite funkcijo WOKRDAY.INTL, ki podpira določanje vikendov.

obisk DELOVNI DAN.INTL Za več podrobnosti.

Če želite delovne dni odšteti od datuma, samo spremenite število dni v negativno vrednost v formuli.

2.16 Dodajte ali odštejte določeno leto, mesec, dneve od datuma

Če želite datumu dodati določeno leto, mesec, dneve, vam lahko uslugo naredi formula, ki združuje funkcijo DATUM, LETO, MESEC in DNI.

Če želite datumu v A1 dodati 2 leto, 30 meseca in 11 dni, uporabite naslednjo formulo:

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

Pritisnite Vnesite ključ, da dobite rezultat.

Če želite odšteti, spremenite vse znake plus (+) v znake minus (-).

---

2.21 Datumu in času dodajte ali odštejte ure/minute/sekunde

Tukaj je nekaj formul za dodajanje ali odštevanje ur, minut ali sekund datumu in času.

Datumu in času dodajte ali odštejte ure

Predvidevamo, da dodamo 3 ure datumu in času (lahko je tudi čas) v celici A2, uporabite formulo kot je ta:

=A2+3/24

Pritisnite Vnesite ključ, da dobite rezultat.

Datumu in času dodajte ali odštejte ure

Predvidevamo, da dodamo 15 minut datumu in času (lahko je tudi čas) v celici A5, uporabite formulo kot je ta:

=A2+15/1440

Pritisnite Vnesite ključ, da dobite rezultat.

Datumu in času dodajte ali odštejte ure

Predpostavimo, da dodamo 20 sekund datumu in času (lahko je tudi čas) v celici A8, uporabite formulo kot je ta:

=A2+20/86400

Pritisnite Vnesite ključ, da dobite rezultat.

2.22 Seštevek časov v 24 urah

Recimo, da obstaja tabela v Excelu, ki beleži delovni čas vseh zaposlenih v enem tednu, da seštejete skupni delovni čas za izračun plačil, lahko uporabite SUM(obseg) da bi dobili rezultat. Toda na splošno bo seštevek rezultatov prikazan kot čas, ki ne presega 24 ur, kot prikazuje spodnji posnetek zaslona. Kako lahko dobite pravilen rezultat?

Pravzaprav morate le formatirati rezultat kot [hh]:mm:ss.

Z desno tipko miške kliknite celico z rezultati, izberite Oblikuj celice v kontekstnem meniju in v pojavnem meniju Oblikuj celice pogovorno okno, izberite po meri s seznama in vnesite [hh]:mm:ss v besedilno polje v desnem delu kliknite OK.
  

Seštevek rezultatov bo prikazan pravilno.

2.23 Datumu dodajte delovni čas brez vikenda in praznika

Tukaj je podana dolga formula za pridobitev končnega datuma, ki temelji na dodajanju določenega števila delovnih ur začetnemu datumu in izključuje vikende (sobote in nedelje) in praznike.

V Excelovi tabeli A11 vsebuje začetni datum in uro, B11 pa delovni čas, v celici E11 in E13 sta delovni začetni in končni čas, celica E15 pa vsebuje praznik, ki bo izključen.

Prosimo, uporabite naslednjo formulo:

=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))

Pritisnite Vnesite ključ, da dobite rezultat.

---

Če imate nameščen Kutools for Excel, samo eno orodje – Datum in ura Helper lahko reši večino izračunov pri seštevanju in odštevanju datuma in časa.

1. Kliknite celico, za katero želite izpisati rezultat, in s klikom uporabite to orodje Kutools > Pomočnik za formulo > Pomočnik za datum in uro.

2. V Ljubljani Pomočnik za datum in čas pogovorno okno, preverite Dodaj možnost oz Odštej možnost, kot jo potrebujete, nato izberite celico ali neposredno vnesite datum in uro, v kateri želite uporabiti Vnos argumentov nato določite leta, mesece, tedne, dneve, ure, minute in sekunde, ki jih želite dodati ali odšteti, nato kliknite Ok. Oglejte si posnetek zaslona:

Rezultat izračuna si lahko ogledate v Rezultat oddelek.

Zdaj je rezultat izpisan, povlecite samodejno ročico čez druge celice, da dobite rezultate.

klik Pomočnik za datum in čas izvedeti več o uporabi te funkcije.

klik Kutools za Excel če želite poznati vse funkcije tega dodatka.

klik Prenos pristojbine da dobite 30-dnevno brezplačno preskusno različico Kutools za Excel

---

2.41 Preverite ali označite, če je datum potekel

Če obstaja seznam potečenih datumov izdelkov, boste morda želeli preveriti in označiti datume, ki so potekli glede na današnji dan, kot prikazuje spodnji posnetek zaslona.

Pravzaprav Pogojno oblikovanje lahko hitro opravi to delo.

1. Izberite datume, ki jih želite preveriti, nato kliknite Domov > Pogojno oblikovanje > Novo pravilo.

2. V Ljubljani Novo pravilo oblikovanja dialog, izberite S formulo določite, katere celice želite formatirati v Izberite vrsto pravila odsek in tip =B2 v polje za vnos (B2 je prvi datum, ki ga želite preveriti) in kliknite oblikovana poskočiti Oblikuj celice pogovorno okno, nato izberite drugačno oblikovanje, da boste pretekli potekle datume, kot jih potrebujete. Kliknite OK > OK.
  

2.42 Vrne konec tekočega meseca/prvi dan naslednjega meseca/a>

Datumi poteka nekaterih izdelkov so na koncu meseca proizvodnje ali prvi dan naslednjega meseca proizvodnje, za hiter seznam datumov poteka glede na datum proizvodnje sledite temu delu.

Dobite konec tekočega meseca

Tukaj je datum izdelave v celici B13, uporabite formulo kot je ta:

=EOMONTH(B13,0)

Pritisnite Vnesite ključ, da dobite rezultat.

Dobite 1. dan naslednjega meseca

Tukaj je datum izdelave v celici B18, uporabite formulo kot je ta:

=EOMONTH(B18,0)+1

Pritisnite Vnesite ključ, da dobite rezultat.

---

3. Izračunajte starost

V tem razdelku so navedene metode za reševanje tega, kako izračunati starost na podlagi danega datuma ali številke serije.

---

3.11 Izračunajte starost glede na datum rojstva

Pridobite starost v decimalnem številu glede na datum rojstva

klik LETNIK za podrobnosti o njegovih argumentih in uporabi.

Če želite na primer pridobiti starost na podlagi seznama rojstnih datumov v stolpcu B2:B9, uporabite naslednjo formulo:

=YEARFRAC(B2,TODAY())

Pritisnite Vnesite in nato ročico za samodejno izpolnjevanje povlecite navzdol, dokler niso izračunane vse starosti.

Nasvet:

1) Decimalno mesto lahko določite, kot ga potrebujete Oblikuj celice dialog.

2) Če želite izračunati starost na določen datum glede na dani datum rojstva, spremenite TODAY() v določen datum v dvojnih narekovajih, kot je =YEARFRAC(B2,"1/1/2021")

3) Če želite pridobiti starost za naslednje leto na podlagi datuma rojstva, preprosto dodajte 1 v formulo, na primer =YEARFRAC(B2,TODAY())+1.

Pridobite starost v celem številu glede na datum rojstva

klik PODATKOV za podrobnosti o njegovih argumentih in uporabi.

Z uporabo zgornjega primera, da dobite starost na podlagi datumov rojstva na seznamu v B2:B9, uporabite naslednjo formulo:

=DATEDIF(B2,TODAY(),"y")

Pritisnite Vnesite tipko, nato pa ročico za samodejno izpolnjevanje povlecite navzdol, dokler niso izračunane vse starosti.

Nasvet:

1) Če želite izračunati starost na določen datum glede na dani datum rojstva, spremenite TODAY() v določen datum v dvojnih narekovajih, kot je =DATEDIF(B2,"1/1/2021","y") .

2) Če želite pridobiti starost za naslednje leto na podlagi datuma rojstva, preprosto dodajte 1 v formulo, na primer =DATUMDIF(B2,DANES(),"y")+1.

3.12 Izračunajte starost v obliki let, mesecev in dni glede na datum rojstva

Če želite izračunati starost na podlagi danega datuma rojstva in prikazati rezultat kot xx let, xx mesecev, xx dni, kot prikazuje spodnji posnetek zaslona, ​​je tukaj dolga formula, ki vam lahko pomaga.

Če želite pridobiti starost v letih, mesecih in dnevih na podlagi rojstnega datuma v celici B12, uporabite naslednjo formulo:

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

Pritisnite Vnesite tipko za pridobitev starosti, nato pa ročico za samodejno izpolnjevanje povlecite navzdol v druge celice.

Nasvet:

Če želite izračunati starost na določen datum glede na dani rojstni datum, spremenite TODAY() v določen datum v dvojnih narekovajih, kot je = =DATEDIF(B12,"1/1/2021","Y")& " Leta, "&DATEDIF(B12,"1/1/2021","YM")&" Meseci, "&DATEDIF(B12,"1/1/2021","MD")&" Dnevi".

3.13 Izračunajte starost glede na datum rojstva pred 1. 1. 1900

V Excelu datuma pred 1/1/1900 ni mogoče vnesti kot datum in čas ali pravilno izračunati. Če pa želite izračunati starost slavne osebe na podlagi danega datuma rojstva (pred 1/11900) in datuma smrti, vam lahko pomaga le koda VBA.

1. Pritisnite druga + F11 tipke za omogočanje Microsoft Visual Basic za aplikacije okno in kliknite Vstavi zavihek in izberite Moduli da ustvarite nov modul.

2. Nato kopirajte in prilepite spodnjo kodo v nov modul.

VBA: Izračunajte starost pred 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. Shranite kodo in se vrnite na list ter izberite celico, v katero postavite izračunano starost, vnesite =AgeFunc(datum rojstva,datum smrti), v tem primeru =AgeFunc(B22,C22), pritisnite tipko Enter, da dobite starost. Po potrebi uporabite ročico za samodejno polnjenje, da uporabite to formulo v drugih celicah.

Če imate Kutools za Excel nameščen v Excelu, lahko uporabite Pomočnik za datum in čas orodje za izračun starosti.

1. Izberite celico, v katero želite postaviti izračunano starost, in kliknite Kutools > Pomočnik za formulo > Pomočnik za datum in čas.

2. V Ljubljani Pomočnik za datum in čas pogovorno okno,

• 1) Preverite Starost možnost;
• 2) Izberite celico z rojstnim datumom ali neposredno vnesite rojstni datum ali kliknite ikono koledarja, da izberete rojstni datum;
• 3) Izberite danes možnost, če želite izračunati trenutno starost, izberite Določen datum možnost in vnesite datum, če želite izračunati starost v preteklosti ali prihodnosti;
• 4) Na spustnem seznamu določite vrsto izhoda;
• 5) Predogled izhodnega rezultata. Kliknite Ok.

klik Pomočnik za datum in čas izvedeti več o uporabi te funkcije.

klik Kutools za Excel če želite poznati vse funkcije tega dodatka.

klik Brezplačen prenos da dobite 30-dnevno brezplačno preskusno različico Kutools za Excel

---

3.31 Pridobite rojstni dan iz ID številke

Če obstaja seznam ID številk, ki uporabljajo prvih 6 števk za zapis datuma rojstva, na primer 920315330 pomeni, da je datum rojstva 03, kako lahko datum rojstva hitro prenesete v drug stolpec?

Zdaj pa vzemimo za primer seznam ID številk, ki se začnejo v celici C2, in uporabimo formulo kot je ta:

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

Pritisnite Vnesite ključ. Nato povlecite ročico za samodejno izpolnjevanje navzdol, da dobite druge rezultate.

Opomba:

V formuli lahko spremenite sklic na svojo potrebo. Na primer, če je ID številka prikazana kot 13219920420392, rojstni dan je 04, lahko formulo spremenite v =MID(C20)&"/"&MID(C1992)&"/ "&MID(C2,8,2), da dobite pravilen rezultat.

3.32 Izračunajte starost iz ID številke

Če obstaja seznam številk ID, ki uporablja prvih 6 števk za zapis datuma rojstva, na primer 920315330 pomeni, da je datum rojstva 03, kako lahko hitro izračunate starost na podlagi vsake številke ID v Excelu?

Zdaj pa vzemimo za primer seznam ID številk, ki se začnejo v celici C2, in uporabimo formulo kot je ta:

=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")

Pritisnite Vnesite ključ. Nato povlecite ročico za samodejno izpolnjevanje navzdol, da dobite druge rezultate.

Opomba:

Če je v tej formuli leto manjše od tekočega leta, se bo leto štelo, da se začne z 20, na primer 200203943 se bo štelo za leto 2020; če je leto večje od trenutnega leta, bo veljalo, da se leto začne z 19, na primer 920420392 bo obravnavano kot leto 1992.

---

Več vadnic za Excel: