Ukusebenzisa i-DATEDIF ukubala imihla, inyanga, okanye iminyaka e-Excel

Bala ixesha okanye umehluko phakathi kwemihla emibini

I-Excel inemisebenzi emininzi eyakhiwe kwimisebenzi yemihla engasetyenziselwa ukubala inani leentsuku phakathi kwemihla emibini.

Umsebenzi ngamnye womsebenzi wenza umsebenzi ohlukeneyo ukwenzela ukuba iziphumo zihluke kumsebenzi omnye ukuya olandelayo. Yiyiphi na oyisebenzisayo, ngoko, kuxhomekeke kwiziphumo ozifunayo.

Umsebenzi we-DATEDIF ungasetyenziselwa ukubala ixesha okanye umda phakathi kwemihla emibini. Eli xesha lingabalelwa ku:

• iintsuku
• iinyanga
• yonke iminyaka

Ukusetyenziswa kolu msebenzi kubandakanya ukucwangciswa okanye iziphakamiso zokubhala ukuchonga ixesha lokulungiselela iphrojekthi ezayo. Ingasetyenziselwa, kunye nomhla wokuzalwa komntu, ukubala iminyaka yakhe, iminyaka, kunye neentsuku .

I-Syntax yeMisebenzi kunye neArguments

I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye iquka igama lomsebenzi, amabakethi kunye neengxabano .

I-syntax yomsebenzi we-DATEDIF yi:

= DATEDIF (qalisa_date, end_date, unit)

qala_date - (efunekayo) umhla wokuqala wexesha elikhethiweyo. Umhla wokuqala wokuqalisa ungangeniswa kule ngxabano okanye isalathisi yesisele kwindawo yendawo kule fom yokusebenzela ingeniswa endaweni yoko.

i-end_date - (efunekayo) umhla wokuphela kwexesha elikhethiweyo. Njenga-Start_date, faka umhla wokugqibela okanye isalathisi yesalathisi kwindawo yolu datha kwiphepha lokusebenzela.

iyunithi (eyayibizwa ngexesha elide) - (elifunekayo) lixelela umsebenzi ukuba ufumane inani leentsuku ("D"), iinyanga ezipheleleyo ("M"), okanye ugcwalise iminyaka ("Y") phakathi kwemihla emibini.

Amanqaku:

1. I-Excel ithatha izibalo zomhla ngokuguqula imihla kumanani omnxeba , eqala ngo-zero ngosuku loqheliselo lweJanuwari 0, 1900 kwiikhompyutha ze-Windows kunye neJanuwari 1, 1904 kwiikhomputha ze-Macintosh.
2. Ingxabano yunithi kufuneka ijikelezwe ngamanqaku okucaphuna njenge "D" okanye "M".

Oku ngakumbi kwiCandelo lokuPhikisana

Ingxabano yeyunithi ingaphinda iquke ukudibanisa kweentsuku, iinyanga, kunye neminyaka ukwenzela ukufumana inani leenyanga phakathi kwemihla emibini kunyaka omnye okanye inani leentsuku phakathi kwemihla emibini ngenyanga leyo.

• "YD" - ibala inani leentsuku phakathi kwemihla emibini ngathi ukuba imihla iyenye ngonyaka (umgca 5 ngasentla).
• "YM" - ibala inani leenyanga phakathi kwemihla emibini nje ukuba imihla iyenjalo ngonyaka (umqolo 6 ngentla).
• "MD" - ibala inani leentsuku phakathi kwemihla emibini nje ukuba imihla iyenye nyanga kunye nomnyaka (umgca 7 ngentla).

I-DATEDIF Iimpazamo zokuPhoswa kweMisebenzi

Ukuba idatha yeengxabano ezahlukahlukeneyo zalo msebenzi ayifakwanga ngokuchanekileyo, ixabiso leempazamo ezilandelayo zivela kwiseli apho umsebenzi we-DATEDIF ukhona:

• #VALUE! Iphutha - libuyiselwe ukuba okanye iqalisa_iyo okanye i- end_date ayikho imihla yangempela (umgca 8 ngasentla, apho iseli ye-A8 iqulethe idatha yombhalo).
• #NUM! Iphutha - ibuyiselwe xa iphelile _date ngumhla wangaphambili kunokuba uqale_date (umqolo 9 ngentla).

Umzekelo: Bala umlinganiselo phakathi kwamaxesha amabini

Inqaku elithakazelisayo malunga neDATEDIF kukuba yinto efihliweyo kuyo ukuba ayibhalwanga neminye imisebenzi yoMhla phantsi kwethebhu yefomula kwi-Excel, oko kuthetha ukuba:

1. akukho bhokisi yencoko yencoko ekhoyo yokungena emsebenzini kunye neengxoxo zayo.
2. i-tooltip ingxabano ayibonisi uluhlu lweengxabano xa igama lomsebenzi lifakwe kwiseli.

Ngenxa yoko, umsebenzi kunye neziphakamiso zayo kufuneka zingenwe ngesandla kwisisele ukuze zisetyenziswe, kubandakanywa ukuthayipha i-comma phakathi kwempikiswano nganye ukuba isebenze njengohluli.

Umzekelo weDATEDIF: Ukubala ukuhluka kwiintsuku

Amanyathelo angezantsi ajonga indlela yokufaka umsebenzi we-DATEDIF okhoyo kwiseli B2 kumfanekiso ongentla apha ubonisa inani leentsuku phakathi kwemihla ye-Meyi 4, 2014 no-Agasti 10, 2016.

1. Cofa kwifowuni B2 ukwenza i- cell esebenzayo- yilapho inani lemihla phakathi kwemihla emibini liya kuboniswa.
2. Uhlobo = datedif ( "kwisisele B2.
3. Cofa kwiseli A2 ukungena kule ferensi yeseli njengengqungquthela ye- start_date yomsebenzi.
4. Thayipha i-comma ( , ) kwi-cell B2 elandelayo kwiseferensi yeseli ye- A2 ukuze usebenze njengohluli phakathi kweengxoxo zokuqala neyesibini.
5. Cofa kwiseli A3 kwispredishithi ukungena kule ferensi yeseli njengengxoxo yokugqibela_date.
6. Thayipha i-comma yesibini ( , ) emva kwereferensi yeseli ye- A3.
7. Kwimpikiswano yunithi , thayipha ileta D kwiingcaphuno ( "D" ) ukuxelela umsebenzi esifuna ukwazi inombolo yenani phakathi kwemihla emibini.
8. Thayipha i-parenthesis yokuvala ")".
9. Cinezela ukungena kwikhibhodi ukugqiba ifom.
10. Inani leentsuku - 829 - kufuneka livele kwiseli B2 kwiphepha lokusebenzela.
11. Xa ucofa kwi-cell B2 ifom epheleleyo = DATEDIF (A2, A3, "D") ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela.