Mokhoa oa ho bala matsatsi lipakeng tsa matsatsi a mabeli ho google sheets.
Haeba u batla ho bala palo ea matsatsi lipakeng tsa matsatsi a mabeli, u ka sebelisa DAYS, DATEDIF, le NETWORKDAYS mesebetsi ho Google Sheets ho etsa joalo. MATSATSI le DATEDIF li bala matsatsi ohle, ha NETWORKDAYS e sa kenyeletse Meqebelo le Lisontaha.
Bala matsatsi ohle pakeng tsa matsatsi a mabeli
Ho bala matsatsi lipakeng tsa matsatsi a mabeli, ho sa tsotelehe hore na kajeno ke letsatsi la beke kapa letsatsi la phomolo, o ka sebelisa mesebetsi ea DAYS kapa DATEDIF.
Ho sebelisa ts'ebetso ea DAYS
Mosebetsi oa DAYS ke oona o bonolo ho o sebelisa, ha feela o sa khathatsehe ka ho se kenyeletse matsatsi a phomolo kapa mafelo-beke. Leha ho le joalo, DAYS e tla hlokomela matsatsi a eketsehileng a bolokiloeng selemong sa leap.
Ho sebelisa DAYS ho bala lipakeng tsa matsatsi a mabeli, bula tafole Lintlha tsa Google Sheets ebe o tobetsa sele e se nang letho. Mofuta =DAYS("01/01/2019","01/01/2018")Tlosa matsatsi a bontšitsoeng ka a hau.
Sebelisa matsatsi ka tatellano ea morao, kahoo beha letsatsi la ho qetela pele, 'me letsatsi la ho qala lekhetlo la bobeli. Ho sebelisa letsatsi la ho qala pele ho tla khutlisetsa DAYS boleng bo fosahetseng.
Joalo ka ha mohlala o kaholimo o bontša, tšebetso ea DAYS e lekanya kakaretso ea matsatsi pakeng tsa matsatsi a mabeli a boletsoeng. Sebopeho sa letsatsi se sebelisitsoeng mohlaleng o ka holimo ke sebopeho sa UK, dd/mm/year. Haeba u US, etsa bonnete ba hore u sebelisa MM/DD/YYYY.
U tla hloka ho sebelisa mokhoa oa kamehla oa letsatsi bakeng sa sebaka sa heno. Haeba u batla ho sebelisa sebopeho se fapaneng, tobetsa Faele> Litlhophiso tsa Spreadsheet 'me u fetole boleng ba Locale sebakeng se seng.
U ka sebelisa ts'ebetso ea DAYS ka litšupiso tsa lisele. Haeba o khetha matsatsi a mabeli liseleng tse arohaneng, o ka thaepa =DAYS(A1, A11), 'me u nkele litšupiso tsa lisele tsa A1 le A11 ka litšupiso tsa lisele tsa hau.
Mohlala o ka holimo, phapang ea matsatsi a 29 e tlalehiloe ho tloha matsatsing a bolokiloeng liseleng tsa E6 le F10.
Ho sebelisa ts'ebetso ea DATEDIF
Mokhoa o mong oa DAYS ke ts'ebetso ea DATEDIF, e u lumellang ho bala palo ea matsatsi, likhoeli kapa lilemo lipakeng tsa matsatsi a mabeli a boletsoeng.
Joalo ka DAYS, DATEDIF e nka matsatsi a phomolo 'me e tla bala matsatsi ohle, ho fapana le ho u lekanyetsa matsatsi a ho sebetsa. Ho fapana le DAYS, DATEDIF ha e sebetse ka tatellano ea morao-rao, kahoo sebelisa letsatsi la ho qala pele le letsatsi la ho qetela la bobeli.
Haeba u batla ho hlakisa matsatsi ka foromo ea hau ea DATEDIF, tobetsa sele e se nang letho ebe u ngola =DATEDIF("01/01/2018","01/01/2019","D"), 'me u behe matsatsi sebakeng sa hau.
Haeba u batla ho sebelisa matsatsi a litšupiso tsa lisele ka mokhoa oa DATEDIF, thaepa =DATEDIF(A7,G7,"D"), 'me u nkele litšupiso tsa lisele tsa A7 le G7 sebaka ka litšupiso tsa lisele tsa hau.
Bala matsatsi a ho sebetsa pakeng tsa matsatsi a mabeli
Mesebetsi ea DAYS le DATEDIF e u lumella ho fumana matsatsi pakeng tsa matsatsi a mabeli, empa a bala matsatsi ohle. Haeba u batla ho bala matsatsi a ho sebetsa feela, 'me u batla ho fokotsa matsatsi a phomolo a eketsehileng, u ka sebelisa tšebetso ea NETWORKDAYS.
MATSATSI a marang-rang a nka Moqebelo le Sontaha e le matsatsi a mafelo-beke, 'me matsatsi ana a tlosoa kamoo a baloang kateng. Joalo ka DATEDIF, NETWORKDAYS e sebelisa letsatsi la qalo pele, e lateloe ke letsatsi la ho qetela.
Ho sebelisa NETWORKDAYS, tobetsa sele e se nang letho ebe u thaepa =NETWORKDAYS(DATE(2018,01,01),DATE(2019,01,01)). Ho sebelisa sehlaha sa DATE ho u lumella ho fetolela lipalo tsa lilemo, likhoeli le matsatsi hore e be nomoro ea letsatsi e latellanang, ka tatellano eo.
Tlosa linomoro tse bontšitsoeng ka linomoro tsa hau tsa selemo, khoeli le letsatsi.
U ka sebelisa litšupiso tsa lisele ka har'a mokhoa oa NETWORKDAYS, ho fapana le ts'ebetso e behiloeng DATE.
ngola =NETWORKDAYS(A6,B6) Sele e se nang letho, 'me u nkele litšupiso tsa lisele tsa A6 le B6 ka litšupiso tsa lisele tsa hau.
Mohlaleng o ka holimo, tšebetso ea NETWORKDAYS e sebelisoa ho bala matsatsi a ho sebetsa lipakeng tsa matsatsi a fapaneng.
Haeba u batla ho qhelela ka thoko matsatsi a itseng lipalong tsa hau, joalo ka matsatsi a mang a phomolo, u ka a kenya qetellong ea mokhoa oa hau oa NETWORKDAYS.
Ho etsa sena, tobetsa sele e se nang letho ebe u thaepa =NETWORKDAYS(A6,B6,{B6:D6}. Mohlaleng ona, A6 ke letsatsi la qalo, B6 ke letsatsi la ho qetela, 'me mofuta oa B6:D6 ke letoto la lisele tse nang le matsatsi a phomolo tse lokelang ho qheleloa ka thoko.
U ka kenya litšupiso tsa lisele sebaka ka matsatsi a hau, u sebelisa ts'ebetso ea DATE e behiloeng, ha u rata. Ho etsa sena, thaepa =NETWORKDAYS(E11,F13,{DATE(2019,11,18),DATE(2019,11,19)}), 'me u nkele litšupiso tsa lisele sebaka le lintlha tsa letsatsi ka linomoro tsa hau.
Mohlaleng o ka holimo, nako e tšoanang e sebelisoa bakeng sa liforomo tse tharo tsa NETWORKDAYS. Ka matsatsi a 11 a tloaelehileng a mosebetsi a tlalehiloeng ka seleng ea B2, pakeng tsa matsatsi a mabeli le a mararo a eketsehileng a phomolo a tlosoa liseleng tsa B3 le B4.
