Ukuqhathanisa izinsuku ku-Excel

Ngokuvamile, abasebenzisi bomhleli wespredishithi badinga ukwenza inqubo enzima njengokuqhathanisa nezinsuku. Lesi senzo singenziwa ngezindlela ezihlukahlukene. Esihlokweni, sizohlaziya ngokuningiliziwe zonke izindlela ezikuvumela ukuthi uqhathanise izinsuku kumhleli wespredishithi.

Isikhathi sokucubungula kumhleli wespredishithi

Umhleli wespredishithi uphatha isikhathi nosuku njengedatha yezinombolo. Uhlelo luguqula lolu lwazi ngendlela yokuthi usuku olulodwa lulingane no-1. Ngenxa yalokho, inkomba yesikhathi iyingxenyana eyodwa. Isibonelo, u-12.00 ngu-0.5. Umhleli wespredishithi uguqula izinkomba zedethi zibe inani lezinombolo, elilingana nenani lezinsuku kusukela ngomhla ka-January 1, 1900 kuya osukwini olushiwo. Isibonelo, uma umsebenzisi eguqula idethi engu-14.04.1987/31881/31881, khona-ke izoba nenani 2. Ngamanye amazwi, izinsuku eziyi-XNUMX zidlulile kunkomba yokuqala. Lo mshini usetshenziswa lapho kubalwa amanani esikhathi. Ukuze ubale inani lezinsuku phakathi kwezinsuku eziyi-XNUMX, kuyadingeka ukususa inkomba yesikhathi esincane enkombeni yesikhathi enkulu.

Kusetshenziswa isitatimende se-DATE kusihleli sethebula

Ukubuka okuvamile komsebenzisi kubukeka kanje: DATE(unyaka, inyanga, usuku). Impikiswano ngayinye iyadingeka ukuthi ibhalwe kusisebenzisi. Kunezindlela ezimbili zokusetha ingxabano. Indlela yokuqala ihlanganisa okokufaka okuvamile kwamanani ezinombolo. Indlela yesibili ihilela ukufaka izixhumanisi zamaseli lapho ulwazi lwezinombolo oludingekayo lutholakala khona. I-agumenti yokuqala iyinani lenombolo ukusuka ku-1900 kuye ku-9999. I-agumenti yesibili iyinani lenombolo ukusuka ku-1 kuye ku-12. I-agumenti yesithathu iyinani lenombolo ukusuka ku-1 ukuya ku-31.

Isibonelo, uma ucacisa inani lenombolo elikhulu kuno-31 njengosuku, usuku olwengeziwe luzodlulela kwenye inyanga. Uma umsebenzisi engena ezinsukwini ezingamashumi amathathu nambili ngoMashi, uzogcina ngelokuqala ku-April.

Isibonelo sokusebenzisa opharetha sibukeka kanje:

Isibonelo sokucacisa inani elikhulu lezinsuku ngoJuni:

Isibonelo esibonisa ukusetshenziswa kwezixhumanisi zeseli njengama-agumenti:

Ukusebenzisa i-opharetha ye-RAZDAT kumhleli wespredishithi

Lo opharetha ubuyisela phakathi kwamanani edethi angu-2. Ukubuka okuvamile komsebenzisi kubukeka kanje: I-RAZDAT(idethi_yokuqala; idethi_yokugcina; ikhodi_ye-designation_of_count_units). Izinhlobo zokubala zezikhawu phakathi kwezinkomba zedethi ezimbili ezishiwo:

• "d" - ibonisa inkomba yokugcina ngezinsuku;
• “m” – ikhombisa inani lezinyanga;
• “y” – ikhombisa isamba seminyaka;
• “ym” – ikhombisa inani lezinyanga, ngaphandle kweminyaka;
• “md” – ikhombisa inani lezinsuku, ngaphandle kweminyaka nezinyanga;
• “yd” – ibonisa inani lezinsuku, ngaphandle kweminyaka.

Kwezinye izinguqulo zomhleli wespredishithi, lapho usebenzisa izimpikiswano ezi-2 ezidlulele, opharetha angase abonise iphutha. Kulokhu, kufaneleka kakhulu ukusebenzisa amanye amafomula.

Isibonelo esibonisa ukusebenza komsebenzisi:

Kumhleli wespredishithi sango-2007, lo opharetha akekho kusithenjwa, kodwa usengasisebenzisa.

Ukusebenzisa i-opharetha engu-YEAR kumhleli wespredishithi

Lo opharetha ikuvumela ukuthi ubuyisele unyaka njengenani eliphelele elihambisana nedethi eshiwo. Inani lenombolo liboniswa kububanzi obusuka ku-1900 ukuya ku-9999. Ifomu elivamile lomsebenzisi ongu-YEAR linempikiswano engu-1. Impikiswano idethi yezinombolo. Kufanele ibhalwe kusetshenziswa isisebenzisi sika-DATE, noma ikhiphe inkomba yokugcina yokubala kwanoma imaphi amanye amafomula. Isibonelo esibonisa ukusebenza komsebenzisi:

Ukusebenzisa i-opharetha ye-MONTH kusihleli sespredishithi

Lo opharetha ikuvumela ukuthi ubuyisele inyanga njengenani eliphelele elihambisana nedethi eshiwo. Inani lenombolo liboniswa kububanzi obusuka ku-1 kuye ku-12. Ifomu elivamile lomsebenzisi we-MONTH linempikiswano engu-1. I-agumenti idethi yenyanga, ebhalwe njengenani lenombolo. Kufanele ibhalwe kusetshenziswa isisebenzisi sika-DATE, noma ikhiphe inkomba yokugcina yokubala kwanoma imaphi amanye amafomula. Kuyaphawuleka ukuthi inyanga ebhalwe ngendlela yombhalo ngeke icutshungulwe kahle umhleli wespredishithi. Isibonelo esibonisa ukusebenza komsebenzisi:

Izibonelo zokusebenzisa i-DAY, WEEKDAY, ne-WEEKDAY opharetha kumhleli wesipredishithi

Lo opharetha ikuvumela ukuthi ubuyisele usuku njengenani eliphelele elihambisana nedethi eshiwo. Inani lenombolo liboniswa kububanzi obusuka ku-1 kuye ku-31. Ifomu elivamile lomsebenzisi we-DAY line-agumenti engu-1. Impikiswano idethi yosuku, ebhalwe njengenani lenombolo. Kufanele ibhalwe kusetshenziswa isisebenzisi sika-DATE, noma ikhiphe inkomba yokugcina yokubala kwanoma imaphi amanye amafomula. Isibonelo esibonisa ukusebenza komsebenzisi:

Umsebenzisi, onegama elithi WEEKDAY, ikuvumela ukuthi ubuyisele inombolo ye-ordinal yosuku lweviki lwedethi enikeziwe. Ngokuzenzakalelayo, umsebenzisi ubheka iSonto njengosuku loku-1 lweviki. Isibonelo esibonisa ukusebenza komsebenzisi:

Umsebenzisi, onegama elithi NOMWEEK, ikuvumela ukuthi ubonise inombolo ye-ordinal yeviki ngosuku oluthile. Isibonelo esibonisa ukusebenza komsebenzisi:

Isibonelo, ngoMeyi 24.05.2015, i-XNUMX isonto lamashumi amabili nambili lonyaka. Njengoba kulotshiwe ngenhla, uhlelo luthatha iSonto njengosuku lokuqala lwesonto.

Impikiswano yesibili ithi 2. Lokhu kuvumela umhleli wesipredishithi ukuthi acabangele uMsombuluko njengesiqalo seviki (kuphela ngale fomula).

Umsebenzisi we-TODAY usetshenziselwa ukusetha idethi yamanje. Lo opharetha akanazo izingxabano. I-opharetha ye-TDATE() isetshenziselwa ukubonisa idethi yamanje nesikhathi.

Isiphetho neziphetho mayelana nokuqhathanisa nezinsuku kumhleli wespredishithi

Sithole ukuthi kunezindlela eziningi nama-opharetha okuqhathanisa amadethi amabili kumhleli wespredishithi. Inketho evame kakhulu ukusebenzisa i-opharetha ye-RAZNDATA, evumela ukuthi ubuyisele umehluko phakathi kwezinsuku ezimbili. Ukwengeza, ungasebenzisa amafomula afanayo ukuze ubuyisele amanani osuku, inyanga, nonyaka. Umsebenzisi ngamunye angazikhethela ngokuzimela indlela elula kakhulu yokuqhathanisa nezinsuku kumhleli wespredishithi.