Ungabala kanjani izinsuku phakathi kwamadethi amabili kumashidi we-google.
Uma ufuna ukubala inani lezinsuku phakathi kwamadethi amabili, ungasebenzisa imisebenzi ethi DAYS, DATEDIF kanye NETWORKDAYS ku-Google AmaSpredishithi ukuze wenze kanjalo. I-DAYS ne-DATEDIF ibala zonke izinsuku, kuyilapho i-NETWORKDAYS ingabandakanyi iMigqibelo namaSonto.
Bala zonke izinsuku phakathi kwamadethi amabili
Ukuze ubale izinsuku phakathi kwamadethi amabili, kungakhathaliseki ukuthi namuhla usuku lwesonto noma iholide, ungasebenzisa imisebenzi ethi DAYS noma DATEDIF.
Ukusebenzisa umsebenzi we-DAYS
Umsebenzi we-DAYS kulula ukuwusebenzisa, inqobo nje uma ungakhathazeki ngokungafaki amaholide noma izinsuku zempelasonto. Kodwa-ke, i-DAYS izoqaphela izinsuku ezengeziwe ezigcinwa onyakeni we-leap.
Ukuze usebenzise DAYS ukubala phakathi kwezinsuku ezimbili, vula ithebula Idatha ye-Google AmaSpredishithi bese uchofoza kuseli elingenalutho. Uhlobo =DAYS("01/01/2019","01/01/2018")Shintsha amadethi abonisiwe uwafake awakho.
Sebenzisa amadethi ngokulandelana okuphambene, ngakho beka idethi yokugcina kuqala, bese ubeka idethi yokuqala okwesibili. Ukusebenzisa idethi yokuqala kuqala kuzobuyisela DAYS kunani eliyinegethivu.
Njengoba isibonelo esingenhla sibonisa, umsebenzi we-DAYS ubala inani eliphelele lezinsuku phakathi kwamadethi amabili ashiwo. Ifomethi yedethi esetshenziswe esibonelweni esingenhla ifomethi yase-UK, dd/mm/year. Uma use-US, qiniseka ukuthi usebenzisa i-MM/DD/YYYY.
Uzodinga ukusebenzisa ifomethi yedethi emisiwe yendawo yangakini. Uma ufuna ukusebenzisa ifomethi ehlukile, chofoza okuthi Ifayela > Izilungiselelo Zespredishithi futhi ushintshe inani lendawo uliyise kwenye indawo.
Ungasebenzisa futhi umsebenzi we-DAYS namareferensi weseli. Uma ukhetha amadethi amabili kumaseli ahlukene, ungabhala =DAYS(A1, A11), futhi esikhundleni sereferensi yeseli A1 ne-A11 ufake ireferensi yakho yeseli.
Kulesi sibonelo esingenhla, umehluko wezinsuku ezingama-29 urekhodwa kusukela kumadethi alondoloziwe kumaseli E6 no-F10.
Ukusebenzisa umsebenzi we-DATEDIF
Enye indlela ye-DAYS umsebenzi we-DATEDIF, okuvumela ukuba ubale inani lezinsuku, izinyanga noma iminyaka phakathi kwamadethi amabili ashiwo.
Njenge-DAYS, i-DATEDIF icabangela izinsuku zokweqa futhi izobala zonke izinsuku, kunokuba ikukhawulele ezinsukwini zokusebenza. Ngokungafani ne-DAYS, i-DATEDIF ayisebenzi ngokulandelana, ngakho sebenzisa idethi yokuqala kuqala bese ugcina idethi yesibili.
Uma ufuna ukucacisa izinsuku kufomula yakho ye-DATEDIF, chofoza kuseli elingenalutho bese uthayipha =DATEDIF("01/01/2018","01/01/2019","D"), futhi esikhundleni sezinsuku ufake ezakho izinsuku.
Uma ufuna ukusebenzisa amadethi asuka kumareferensi eseli kufomula ethi DATEDIF, thayipha =DATEDIF(A7,G7,"D"), futhi esikhundleni sereferensi yeseli ye-A7 ne-G7 ufake ireferensi yakho yeseli.
Bala izinsuku zokusebenza phakathi kwamadethi amabili
Imisebenzi ethi DAYS kanye ne-DATEDIF ikuvumela ukuthi uthole izinsuku phakathi kwamadethi amabili, kodwa ibala zonke izinsuku. Uma ufuna ukubala izinsuku zokusebenza kuphela, futhi ufuna ukukhipha amaholide angeziwe, ungasebenzisa umsebenzi we-NETWORKDAYS.
IZINDABA zokuxhumana zithatha uMgqibelo neSonto njengezinsuku zangezimpelasonto, lezi zinsuku zibanjwa njengoba zibaliwe. Njenge-DATEDIF, NETWORKDAYS isebenzisa idethi yokuqala kuqala, ilandelwe idethi yokugcina.
Ukuze usebenzise i-NETWORKDAYS, chofoza iseli engenalutho bese uthayipha =NETWORKDAYS(DATE(2018,01,01),DATE(2019,01,01)). Ukusebenzisa umsebenzi we-DATE obekwe esidlekeni kukuvumela ukuthi uguqule izinombolo zeminyaka, izinyanga, nezinsuku zibe inombolo yedethi elandelanayo, ngalolo hlelo.
Shintsha izinombolo ezibonisiwe ufake izinombolo zakho zonyaka, inyanga nedethi.
Ungakwazi futhi ukusebenzisa amareferensi eseli kufomula NETWORKDAYS, esikhundleni somsebenzi ofakwe DATE.
bhala =NETWORKDAYS(A6,B6) Iseli elingenalutho, bese ubuyisela amareferensi eseli ye-A6 ne-B6 ngezinkomba zakho zeseli.
Esibonelweni esingenhla, umsebenzi we-NETWORKDAYS usetshenziselwa ukubala izinsuku zokusebenza phakathi kwamadethi ahlukene.
Uma ufuna ukukhipha izinsuku ezithile ezibalweni zakho, njengamaholide athile, ungawengeza ekupheleni kwefomula yakho YE-NETWORKDAYS.
Ukuze wenze lokhu, chofoza iseli elingenalutho bese uthayipha =NETWORKDAYS(A6,B6,{B6:D6}. Kulesi sibonelo, i-A6 idethi yokuqala, i-B6 idethi yokugcina, futhi ububanzi B6:D6 buyibanga lamaseli aqukethe amaholide azokhishwa.
Ungashintsha amareferensi eseli ngezinsuku zakho, usebenzisa umsebenzi owenziwe DATE, uma uthanda. Ukuze wenze lokhu, thayipha =NETWORKDAYS(E11,F13,{DATE(2019,11,18),DATE(2019,11,19)}), futhi esikhundleni sereferensi yeseli nemibandela yedethi ufake izinombolo zakho.
Esibonelweni esingenhla, ibanga ledethi elifanayo lisetshenziswa kumafomula amathathu NETWORKDAYS. Ngezinsuku zokusebenza ezijwayelekile eziyi-11 ezibikwe kuseli B2, phakathi kwezinsuku ezimbili nezintathu zeholide ezengeziwe ziyasuswa kumaseli B3 no-B4.
