Excel YEARFRAC Pom Cov Feem Ntawm Ib Xyoo Ntawm Hnub

2024 Tus sau: Abigail Brown | [email protected]. Kawg hloov kho: 2024-01-07 19:03

The YEARFRAC muaj nuj nqi suav cov feem ntawm ib xyoo sawv cev los ntawm cov hnub ntawm ob hnub (start_date thiab end_date).

Lwm yam haujlwm Excel tuaj yeem pom cov hnub ntawm ob hnub, tab sis lawv tsuas yog txwv kom rov qab tus nqi hauv xyoo, hli, hnub, lossis sib xyaw ntawm peb.

YEARFRAC, ntawm qhov tod tes, rov qab qhov sib txawv ntawm ob hnub nyob rau hauv daim ntawv lej txiav, xws li 1.65 xyoo, kom cov txiaj ntsig tuaj yeem siv ncaj qha rau hauv lwm cov kev suav..

Cov kev suav no tuaj yeem suav nrog cov txiaj ntsig xws li tus neeg ua haujlwm ntev ntawm kev pabcuam lossis feem pua yuav them rau cov haujlwm txhua xyoo uas raug txiav tawm ntxov xws li cov txiaj ntsig kev noj qab haus huv.

Cov lus qhia no siv rau Excel 2019, 2016, 2013, 2010, thiab Excel rau Microsoft 365.

YEARFRAC Function Syntax and Arguments

A muaj nuj nqi syntax hais txog kev teeb tsa ntawm txoj haujlwm thiab suav nrog cov haujlwm lub npe, cov hlua khi, thiab cov lus sib cav. Lub syntax rau YEARFRAC muaj nuj nqi yog:

=YEARFRAC(Start_date, End_date, Basis)

Start_date (yuav tsum tau) yog thawj hnub sib txawv; Qhov kev sib cav no tuaj yeem yog lub xov tooj ntawm tes siv rau qhov chaw ntawm cov ntaub ntawv hauv daim ntawv ua haujlwm lossis hnub pib tiag tiag hauv tus lej xov tooj.

End_date (yuav tsum tau) yog hnub thib ob hloov pauv. Tib qhov kev sib cav yuav tsum siv raws li cov lus teev tseg rau Start_date.

Basis (yeem) yog tus nqi xws li xoom mus rau plaub uas qhia Excel hnub twg suav txoj hauv kev siv nrog cov haujlwm.

• 0 lossis tshem tawm - 30 hnub hauv ib hlis / 360 hnub hauv ib xyoos (U. S. NASD)
• 1 - Muaj pes tsawg hnub hauv ib hlis / Tus lej tseeb ntawm ib xyoos
• 2 - Muaj pes tsawg hnub hauv ib hlis / 360 hnub hauv ib xyoos
• 3 - Muaj pes tsawg hnub hauv ib hlis / 365 hnub hauv ib xyoos
• 4 - 30 hnub hauv ib hlis / 360 hnub hauv ib xyoos (European)

Ntawm cov kev xaiv rau basis kev sib cav, tus nqi ntawm 1muab qhov tseeb tshaj plaws rau suav hnub hauv ib hlis thiab hnub hauv ib xyoos.

Kev sib txawv ntawm hnub ib hlis thiab hnub hauv ib xyoos rau Basis kev sib cav ntawm YEARFRACmuaj nuj nqi vim muaj kev lag luam hauv ntau yam cov kev lag luam, xws li kev sib koom ua lag luam, kev lag luam, thiab nyiaj txiag, muaj cov kev xav tau sib txawv rau lawv cov kev siv nyiaj txiag.

YEARFRAC rov qab VALUE! tus nqi yuam kev yog Start_date lossis End_date tsis yog hnub siv tau.

YEARFRAC rov qab NUM! tus nqi yuam kev yog tias Basis kev sib cav tsawg dua xoom lossis ntau dua plaub.

YEARFRAC Function Piv txwv

Raws li tau pom hauv daim duab saum toj no, qhov piv txwv no yuav siv qhov YEARFRAC ua haujlwm hauv cell E3 txhawm rau nrhiav lub sijhawm ntev nruab nrab ntawm ob hnub - Lub Peb Hlis 9, 2012, thiab Kaum Ib Hlis 1, 2013.

Hauv qhov piv txwv no, koj yuav siv cov ntaub ntawv xovtooj ntawm qhov chaw pib thiab hnub kawg txij li lawv feem ntau yooj yim ua haujlwm nrog dua li nkag mus rau cov lej hnub tim.

Koj tseem tuaj yeem ua cov kauj ruam xaiv los txo cov naj npawb ntawm cov lej hauv cov lus teb los ntawm cuaj mus rau ob siv ROUND ua haujlwm.

Pib los ntawm kev nkag mus rau hauv cov ntaub ntawv mus rau cells D1 rau E2, raws li pom hauv daim duab saum toj no. Cell E3 yog qhov twg tus qauv yuav mus.

Siv lub DATE muaj nuj nqi los sau cov lus pib thiab hnub kawg kev sib cav los tiv thaiv cov teeb meem uas tuaj yeem tshwm sim yog Excel txhais cov hnub ua cov ntaub ntawv.

Hnub ua haujlwm syntax yog raws li hauv qab no rau qhov piv txwv no:

E1 -=DATE(2012, 3, 9)
E2 -=DATE(2013, 11, 1)

Nkag mus rau YEARFRAC Function
Hauv qhov piv txwv no, koj yuav nkag mus rau YEARFRAC ua haujlwm rau cell E3 txhawm rau xam lub sijhawm ntawm ob hnub hauv hlwb E1 and E2.







Click on cell E3 - qhov no yog qhov uas cov txiaj ntsig ntawm kev ua haujlwm yuav tshwm sim.

Click on the Formulas tab ntawm ribbon menu.

Xaiv Hnub thiab Sijhawm los ntawm ribbonqhib lub luag haujlwm poob.
Siv lub DATE muaj nuj nqi los nkag rau cov lus pib thiab hnub kawg kev sib cav tiv thaiv cov teeb meem uas tuaj yeem tshwm sim yog tias cov hnub raug txhais ua cov ntaub ntawv.

Click on YEARFRAC hauv daim ntawv txhawm rau coj mus rau Formula Builder.
Click on the Start_date kab.
Click on cell E1 hauv daim ntawv ua haujlwm kom nkag mus rau ntawm tes siv.
Click on the End_date kab.
Click on cell E2 hauv daim ntawv ua haujlwm kom nkag mus rau ntawm tes siv.
Click on the Basis kab.
Cua tus lej 1 ntawm kab no siv cov naj npawb ntawm cov hnub hauv ib hlis thiab cov lej tiag tiag hauv ib xyoos hauv kev suav
Click OK kom ua tiav txoj haujlwm.
Tus nqi 1.647058824 yuav tsum tshwm sim hauv cell E3 uas yog lub sijhawm ntev hauv xyoo ntawm ob hnub.
Koj daim ntawv ua haujlwm yuav tso tawm ntau dua lossis tsawg dua tus lej lej nyob ntawm koj qhov chaw.


Nesting the ROUND and YEARFRAC Functions
Txhawm rau ua kom cov txiaj ntsig tau yooj yim ua haujlwm nrog, koj tuaj yeem hloov tus nqi hauv cell E3 rau ob qhov lej lej los ntawm kev ua zes ROUNDthiab YEARFRACua haujlwm. Ua li no, ntaus ROUND tom qab qhov sib npaug (=) kos npe, thiab , 2 nyob rau hauv pem hauv ntej ntawm kab lus kawg. Cov mis formula yog:
=ROUND(YEARFRAC(E1, E2, 1), 2)
Cov lus teb rau 1.65.