Mid ka mid ah xaddidaadyada taariikhaha kala-goynta ee Exce^{(subtracting dates in Exce)} l waa in codsigu ku siin karo oo keliya tirada maalmaha, tirada bilaha, ama tirada sannadaha si gaar ah, halkii uu ka ahaan lahaa tiro la isku daray.

Nasiib wanaag, Microsoft waxay ku dartay shaqada ^(Microsoft)Excel ee la dhisay si ay kuu siiso faraqyo sax ah oo u dhexeeya labada taariikhood ee xaashida shaqada. Baro sida loo xisaabiyo qadarka wakhtiga u dhexeeya laba taariikhood oo sax ah gudaha Excel .

Isticmaalka shaqada YEARFRAC ee Excel

Adigoo isticmaalaya shaqada YEARFRAC , waxaad xisaabin kartaa faraqa saxda ah ee u dhexeeya laba taariikhood sababtoo ah si ka duwan hababka kale ee soo celisa natiijada celceliska, shaqadani waxay soo celisaa natiijada tobanle si ay u muujiso jajabyada sanadka.

Hawsha YEARFRAC , si kastaba ha ahaatee, waxay u baahan tahay aqoon ka badan inta badan shaqooyinka kale. Habka aasaasiga ah ee isticmaalka shaqadan waa:

=YEARFRAC(start_date, end_date, basis)

Start_date waa taariikhda doorsoomiyaha koowaad, end_date waa taariikhda doorsoomiyaha labaad , saldhiguna^(basis) waa male-awaalka hoos yimaada Excel waa inuu soo celiyo natiijada xisaabinta. Waa saldhigga ay tahay inaad ka taxaddarto markaad isticmaalayso shaqada YEARFRAC .

Ka soo qaad^(Suppose) inaad haysatid warqad shaqo oo Excel ah oo sidan u eg oo aad rabto inaad xisaabiso farqiga saxda ah ee u dhexeeya labada taariikh ee A1 iyo A2:

Isku soo wada duuboo laba meelood oo jajab tobanle ah, Excel wuxuu soo celiyay natiijada 3.16 sano iyadoo la adeegsanayo shaqada YEARFRAC . Si kastaba ha ahaatee, maadaama aynaan ku darin doorsoomaha aasaasiga ah isla'egta, Excel waxa ay u qaadatay in ay jiraan 30 maalmood oo sax ah bishii kasta oo bixinaysa dhererka sanadka oo dhan 360 maalmood.

Waxaa jira shan qiyam oo aad u isticmaali karto doorsoomaha aasaasiga^(basis) ah, mid kastaa wuxuu u dhigmaa male kale oo ku saabsan dhererka halka sano.

Marka loo eego dukumeentiga caawinta, ama ka-tagitaanka ama adeegsiga qiimaha 0 ee is beddelka aasaasiga ah ayaa ku qasbaya ^(basis)Excel inuu qaato heerka NASD US^{(US NASD)} ee 30-maalmood iyo 360-maalmood sano.

Tani waxay u ekaan kartaa qariib ilaa aad ka ogaato in xisaabin maaliyadeed oo badan lagu sameeyay male-awaalkan. Dhammaan qiimayaasha suurtagalka ah ee doorsoomaha aasaasiga ah waxaa ka mid ah:^(basis)

• 0 – US NASD 30 maalmood bilo/360 maalmood sano

• 1- Maalmaha dhabta ah ee bilaha/maalmaha dhabta ah ee sanadaha

• 2-Maalmaha dhabta ah ee bilaha/360 maalmood ee sanadaha

• 3-Maalmaha dhabta ah ee bilaha/365 maalmood ee sanadaha

• 4 - Yurub 30 maalmood bilaha / 360 maalmood ee sanadaha

Ogaysii in qiimaha doorsoomaha saldhiga^(basis) soo celin doona tirada ugu saxsan inta u dhaxaysa laba taariikhood uu yahay 1. Hoos waxa ah natiijooyinka isticmaalka mid kasta oo ka mid ah qiyamka doorsoomaha saldhiga :^(basis)

Inkasta oo qaar ka mid ah^(basis) qiyamka doorsoomayaasha salku ay u ekaan karaan kuwo qariib ah, isku darka malo-awaalka kala duwan ee ku saabsan dhererka hal bil iyo halka sano ayaa loo adeegsadaa dhinacyo badan sida dhaqaalaha, maaliyadda, iyo maareynta hawlaha.

Si loo isbarbardhigo inta u dhaxaysa bilaha leh tiro maalmo kala duwan (ka fikir Febraayo^(February) iyo Maarso^(March) ) iyo inta u dhaxaysa sannadaha leh tiro maalmo kala duwan (ka fikir sanadaha leap-ka iyo sannadaha kalandarka), xirfadahani waxay inta badan sameeyaan malo yaab leh oo qofka caadiga ahi aanu samayn doonin.

Gaar ahaan faa'iido u leh maalgeliyaha ayaa isticmaalaya malo-awaalka ay bixiso doorsoomaha aasaasiga ah si loo xisaabiyo ^(basis)APR^(APRs) -yada iyo APY^(APYs) -yada iyadoo lagu saleynayo xaalado isku-darka xiisaha ee kala duwan. Dulsaarka waxaa loo xisaabin karaa si joogto ah, maalin, toddobaadle, bille, sannadle ama xataa muddo dhowr sano ah.

Iyadoo malo-awaal lagu dhex dhisay shaqada YEARFRAC , waxaad hubin kartaa in xisaabintaadu tahay mid sax ah oo la barbar dhigi karta xisaabinta kale iyadoo la isticmaalayo isla malo.

Sida kor lagu sheegay, qiimaha 1 ee doorsoomayaasha salka^(basis) ayaa farsamo ahaan ugu saxsan. Haddii aad shaki ku jirto, dooro 1 ilaa aad ka hubsato malo-awaalka aad rabto inaad samayso ee ku saabsan dhererka bisha iyo sanadka. Ku raaxayso!

How to Use the YEARFRAC Function in Excel

One of the limitations of subtracting dates in Excel is that the application can only give you either the number of days, the number of months, or the number of years separately, rather than a combined number.

Luckily, Microsoft included a built in Excel function to give you precise differences between two dates in a worksheet. Learn how to calculate the amount of time between two dates with precision in Excel.

Using the YEARFRAC function in Excel

Using the YEARFRAC function, you can calculate the precise difference between two dates because unlike other methods that return an integer result, this function returns a decimal result to indicate fractions of a year.

The YEARFRAC function, however, requires a bit more knowledge than most other functions. The basic formula for using this function is:

=YEARFRAC(start_date, end_date, basis)

Start_date is the first variable date, end_date is the second variable date, and basis is the assumption under which Excel should return the result of the calculation. It’s the basis with which you must be careful when using the YEARFRAC function.

Suppose you have an Excel worksheet that looks something like this and you want to calculate the precise difference between the two dates in A1 and A2:

Rounded to two decimal places, Excel returned a result of 3.16 years using the YEARFRAC function. However, because we did not include the basis variable in the equation, Excel assumed that there are exactly 30 days in every month giving a total year’s length of just 360 days.

There are five values that you can use for the basis variable, each corresponding to a different assumption about the length of one year.

According to the help document, either omitting or using a value of 0 for the basis variable forces Excel to assume the US NASD standard of 30-day months and 360-day years.

This may seem strange until you realize that many financial calculations are made under these assumptions. All of the possible values for the basis variable include:

• 0 – US NASD 30 day months/360 day years

• 1 – Actual days in the months/Actual days in the years

• 2 – Actual days in the months/360 days in the years

• 3 – Actual days in the months/365 days in the years

• 4 – European 30 days in the months/360 days in the years

Notice that the value for the basis variable that will return the most accurate number between two dates is 1. Below are the results of using each of the values for the basis variable:

Although some of the values for the basis variable may seem strange, different combinations of assumptions about the length of one month and one year are used in several fields such as economics, finance, and operations management.

In order to remain comparable between months with different numbers of days (think February vs. March) and between years with different numbers of days (think leap years vs. calendar years), these professions often make strange assumptions that the average person would not.

Particularly useful to the financier is using the assumptions offered by the basis variable to calculate APRs and APYs based on different interest compounding scenarios. Interest can be calculated continuously, daily, weekly, monthly, yearly or even span multiple years.

With the assumptions built into the YEARFRAC function, you can be sure your calculations are accurate and comparable with other calculations using the same assumptions.

As stated above, a value of 1 for the basis variable is technically the most accurate. If in doubt, choose 1 unless you are certain of the assumptions you want to make regarding the length of a month and a year. Enjoy!

Related posts

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida loo isticmaalo Excel AutoRecover iyo AutoBackup Features

• Sida loo isticmaalo VLOOKUP gudaha Excel

• Sida loo xisaabiyo kala duwanaanshaha Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• Sida Loo Sameeyo Kaarka Salaan ee MS Word

• Sida loo kala saaro Magacyada Hore iyo Dambe ee Excel

• Sida loo duubo Macro gudaha Excel

• Hagaha Dhammaan Kordhinta Faylka Excel & Waxa ay ula jeedaan

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Sida Si Toos ah Loogu Soo Kabto Dukumeenti Word OneDrive

• Sida loo isticmaalo COUNTIFS, SUMIFS, AVERAGEIFS gudaha Excel