Yuav ua li cas xam hnub ntawm ob hnub hauv google nplooj ntawv.
Yog tias koj xav suav cov hnub ntawm ob hnub, koj tuaj yeem siv HNUB, DATEDIF thiab NETWORKDAYS cov haujlwm hauv Google Sheets ua li ntawd. HNUB thiab DATEDIF suav tag nrho cov hnub, thaum NETWORKDAYS tsis suav hnub Saturday thiab Hnub Sunday.
Suav txhua hnub ntawm ob hnub
Txhawm rau xam cov hnub ntawm ob hnub, tsis hais hnub no yog hnub ua haujlwm lossis hnub so, koj tuaj yeem siv HNUB lossis DATEDIF cov haujlwm.
Siv DAYS muaj nuj nqi
DAYS muaj nuj nqi yog qhov yooj yim siv, tsuav yog koj tsis txhawj xeeb txog kev tsis suav hnub so lossis hnub so. Txawm li cas los xij, DAYS yuav sau cov hnub ntxiv uas khaws cia hauv xyoo leap.
Txhawm rau siv DAYS los suav nruab nrab ntawm ob hnub, qhib lub rooj Google Sheets cov ntaub ntawv thiab nyem rau ntawm qhov khoob ntawm tes. Hom =DAYS("01/01/2019","01/01/2018")Hloov cov hnub tso tawm nrog koj tus kheej.
Siv cov hnub nyob rau hauv qhov kev txiav txim rov qab, yog li muab hnub kawg ua ntej, thiab hnub pib thib ob. Siv hnub pib ua ntej yuav rov DAYS rau tus nqi tsis zoo.
Raws li qhov piv txwv saum toj no qhia, DAYS muaj nuj nqi suav tag nrho cov hnub ntawm ob hnub teev. Hnub tim hom ntawv siv hauv qhov piv txwv saum toj no yog UK hom, dd/mm/xyoo. Yog tias koj nyob hauv Asmeskas, nco ntsoov siv MM/DD/YYYY.
Koj yuav tau siv lub hnub qub hom ntawv rau koj lub zos. Yog tias koj xav siv hom ntawv sib txawv, nyem Cov Ntaub Ntawv> Spreadsheet Settings thiab hloov tus nqi Locale mus rau lwm qhov chaw.
Koj tseem tuaj yeem siv DAYS muaj nuj nqi nrog cov ntaub ntawv ntawm tes. Yog tias koj xaiv ob hnub nyob rau hauv nyias nyias, koj tuaj yeem ntaus =DAYS(A1, A11), thiab hloov cov ntaub ntawv xovtooj A1 thiab A11 nrog koj tus kheej cov ntaub ntawv xovtooj.
Hauv qhov piv txwv saum toj no, qhov sib txawv ntawm 29 hnub raug kaw los ntawm cov hnub khaws tseg hauv hlwb E6 thiab F10.
Siv DATEDIF muaj nuj nqi
Ib qho ntxiv rau DAYS yog DATEDIF muaj nuj nqi, uas tso cai rau koj suav cov hnub, hli lossis xyoo ntawm ob hnub teev.
Zoo li DAYS, DATEDIF yuav siv sij hawm rau hauv cov hnub leap thiab yuav suav tag nrho cov hnub, tsis txwv koj rau hnub ua haujlwm. Tsis zoo li HNUB, DATEDIF tsis ua haujlwm rov qab, yog li siv hnub pib thawj zaug thiab hnub kawg thib ob.
Yog tias koj xav qhia hnub tim hauv koj cov qauv DATEDIF, nyem rau ntawm lub xov tooj dawb thiab ntaus =DATEDIF("01/01/2018","01/01/2019","D"), thiab hloov cov hnub nrog koj tus kheej cov hnub.
Yog tias koj xav siv hnub los ntawm cov ntaub ntawv xovtooj ntawm tes hauv DATEDIF cov qauv, ntaus =DATEDIF(A7,G7,"D"), thiab hloov A7 thiab G7 cov ntaub ntawv xov tooj ntawm tes nrog koj tus kheej cov ntaub ntawv ntawm tes.
Suav cov hnub ua haujlwm ntawm ob hnub
DAYS thiab DATEDIF cov haujlwm tso cai rau koj nrhiav cov hnub nruab nrab ntawm ob hnub, tab sis lawv suav txhua hnub. Yog tias koj xav suav cov hnub ua haujlwm nkaus xwb, thiab koj xav txiav cov hnub so ntxiv, koj tuaj yeem siv NETWORKDAYS muaj nuj nqi.
NETWORKDAYS kho hnub Saturday thiab hnub Sunday raws li hnub so, nrog rau cov hnub no txiav tawm raws li lawv suav. Zoo li DATEDIF, NETWORKDAYS siv hnub pib ua ntej, ua raws li hnub kawg.
Txhawm rau siv NETWORKDAYS, nyem rau ntawm lub xov tooj dawb thiab ntaus ntawv =NETWORKDAYS(DATE(2018,01,01),DATE(2019,01,01)). Siv lub nested DATE muaj nuj nqi tso cai rau koj hloov cov naj npawb ntawm xyoo, lub hlis, thiab hnub tim mus rau hauv ib tug sequential hnub naj npawb, nyob rau hauv qhov kev txiav txim.
Hloov cov lej qhia nrog koj tus kheej xyoo, hli, thiab hnub tim.
Koj tseem tuaj yeem siv cov ntaub ntawv xov tooj ntawm tes hauv NETWORKDAYS cov qauv, tsis yog lub zes DATE muaj nuj nqi.
sau =NETWORKDAYS(A6,B6) Cell dawb, thiab hloov cov ntaub ntawv A6 thiab B6 ntawm tes nrog koj tus kheej cov ntaub ntawv ntawm tes.
Hauv qhov piv txwv saum toj no, NETWORKDAYS muaj nuj nqi yog siv los xam cov hnub ua haujlwm ntawm cov hnub sib txawv.
Yog tias koj xav tshem tawm qee hnub los ntawm koj qhov kev suav, xws li qee cov hnub so, koj tuaj yeem ntxiv lawv thaum kawg ntawm koj cov qauv NETWORKDAYS.
Txhawm rau ua qhov no, nyem rau ntawm lub xov tooj dawb thiab ntaus ntawv =NETWORKDAYS(A6,B6,{B6:D6}. Hauv qhov piv txwv no, A6 yog hnub pib, B6 yog hnub kawg, thiab qhov ntau B6: D6 yog ntau lub hlwb uas muaj cov hnub so kom tsis suav nrog.
Koj tuaj yeem hloov cov ntaub ntawv xov tooj ntawm tes nrog koj tus kheej cov hnub, siv cov haujlwm DATE, yog tias koj xav tau. Ua li no, ntaus =NETWORKDAYS(E11,F13,{DATE(2019,11,18),DATE(2019,11,19)}), thiab hloov cov ntaub ntawv xov tooj ntawm tes thiab hnub cov qauv nrog koj tus lej.
Hauv qhov piv txwv saum toj no, tib hnub ntau yog siv rau peb NETWORKDAYS qauv. Nrog rau 11 hnub ua haujlwm tus qauv qhia hauv cell B2, nruab nrab ntawm ob thiab peb hnub so ntxiv raug tshem tawm hauv hlwb B3 thiab B4.
