Haddii aad haysato xaashi Excel ah oo leh taariikho badan, fursadaha waa inaad ugu dambeyntii u baahan doontaa inaad xisaabiso faraqa u dhexeeya qaar ka mid ah taariikhahaas. Waxaa laga yaabaa inaad rabto inaad aragto inta bilood ee ay kugu qaadatay inaad iska bixiso deyntaada ama imisa maalmood ayay kugu qaadatay inaad lumiso qaddar cayiman oo miisaan ah?

Xisaabinta farqiga u dhexeeya taariikhaha gudaha Excel waa sahlan tahay, laakiin waxay noqon kartaa mid adag iyadoo ku xiran sida aad rabto inaad u xisaabiso qiyamka qaarkood. Tusaale ahaan, haddii aad rabto inaad ogaato tirada bilood ee u dhaxaysa 2/5/2016 iyo 1/15/2016, jawaabtu ma inay noqotaa 0 ama 1? Dadka qaar ayaa laga yaabaa inay dhahaan 0 maadaama aysan ahayn bil buuxda inta u dhaxaysa taariikhaha qaar kalena waxay odhan karaan 1 sababtoo ah waa bil ka duwan.

Maqaalkan, waxaan ku tusi doonaa sida loo xisaabiyo farqiga u dhexeeya laba taariikhood si loo helo tirada maalmaha, bilaha iyo sannadaha oo leh qaabab kala duwan, taas oo ku xidhan hadba sida aad door bidayso.

Maalmo u dhexeeya Laba Taariikhood

Xisaabinta ugu fudud ee aan sameyn karno waa inaan helno tirada maalmaha u dhexeeya laba taariikhood. Waxa wanaagsan ee ku saabsan xisaabinta maalmaha waa in runtii ay jirto hal dariiqo oo lagu xisaabiyo qiimaha, markaa uma baahnid inaad ka walwasho qaabab kala duwan oo ku siinaya jawaabo kala duwan.

Tusaalahayga sare, waxaan haystaa laba taariikhood oo lagu kaydiyay unugyada A2 iyo A3. Midigta, waxaad arki kartaa farqiga u dhexeeya labadaas taariikh waa 802 maalmood. In Excel , had iyo jeer waxaa jira siyaabo badan oo lagu xisaabiyo isla qiimaha waana waxa aan halkan ku sameeyay. Aan eegno qaacidooyinka:

Qaacidada koowaad waa uun kala-goyn fudud oo ah labada taariikhood, A3 – A2 . Excel wuu ogyahay inay taariikh tahay wuxuuna si fudud u xisaabiyaa tirada maalmaha u dhexeeya labadaas taariikh. Fudud^(Easy) oo aad u toosan-hore. Intaa waxaa dheer, waxaad sidoo kale isticmaali kartaa shaqada DAYS .

=DAYS(A3, A2)

Shaqadani waxay qaadataa laba doodood: taariikhda dhammaadka iyo taariikhda bilawga. Haddii aad bedesho taariikhaha ku jira qaacidada, waxaad heli doontaa kaliya tiro taban. Ugu dambeyntii, waxaad isticmaali kartaa shaqo la yiraahdo DATEDIF , oo ku jirta Excel laga bilaabo Lotus 1-2-3 maalmood, laakiin aan ahayn qaacido si rasmi ah loo taageeray gudaha Excel .

=DATEDIF(A2, A3, "D")

Markaad qorto qaacidada, waxaad arki doontaa in Excel uusan ku siinin wax talobixin ah oo ku saabsan beeraha, iwm. Nasiib wanaag, waxaad arki kartaa syntax iyo dhammaan doodaha la taageeray ee shaqada DATEDIF^{(DATEDIF function)} halkan.

Sida gunno dheeri ah, ka waran haddii aad rabto inaad xisaabiso tirada maalmaha shaqada inta u dhaxaysa laba taariikhood? Taasi waa sahlan tahay sidoo kale sababtoo ah waxaan haysanaa qaacido ku dhisan:

=NETWORKDAYS(startDate,endDate)

Xisaabinta tirada maalmaha iyo maalmaha shaqada waa sahlan tahay in ku filan, markaa aan hadda ka hadalno bilaha hadda.

Bilaha u dhexeeya Laba Taariikhood

Xisaabinta ugu dhibka badan waa tirada bilaha sababta oo ah sida aad u soo ururin karto ama u soo koobin karto iyada oo ku xidhan haddii ay tahay bil dhammaystiran ama bil qayb ah. Hadda waxaa jira bilo^(MONTHS) shaqo oo Excel ah , laakiin aad ayey u xaddidan tahay sababtoo ah waxay eegi doontaa oo keliya bisha marka la xisaabinayo faraqa ee maaha sanadka. Tani waxay ka dhigan tahay inay faa'iido u leedahay xisaabinta faraqa u dhexeeya laba bilood isla sanadkaas.

Mar haddii ay taasi macno la'aan tahay, aynu eegno qaar ka mid ah qaacidooyinka noo heli doona jawaabta saxda ah. Microsoft ayaa ^(Microsoft)halkan^(here) ku bixisay kuwan , laakiin haddii aad caajis u tahay inaad booqato isku xirka, waxaan sidoo kale ku siiyay hoosta.

Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

Hadda kuwanu waa laba qaacido oo dhaadheer oo adag, runtiina uma baahnid inaad fahanto waxa socda. Halkan waxaa ah qaababka Excel:

Ogsoonow in aad wax ka beddesho caanaha wareega ee unugga dhabta ah laftiisa sababtoo ah sabab aan caadi ahayn awgeed qaacidada oo dhami kama soo baxayso baarka caanaha. Si aad u aragto qaacidada unugga laftirkeeda, dhagsii tabka Formulaska^(Formulas) ka dibna dhagsii Show Formulas .

Haddaba maxay tahay natiijada kama dambaysta ah ee labada qaacido ee tusaalahayaga hadda? Aan hoos aragno:

Isku^(Round) soo wada duuboo waxa ay i siinaysaa 27 bilood oo hooskuna waxa uu i siinayaa 26 bilood, taas oo sax ah, iyada oo ku xidhan hadba sida aad doonayso in aad u eegto. Ugu dambeyntii, waxaad sidoo kale isticmaali kartaa shaqada DATEDIF , laakiin kaliya waxay xisaabisaa bilo buuxa, markaa kiiskeena jawaabta waxay soo celisaa waa 26.

=DATEDIF(A2, A3, "M")

Sannado U Dhaxeeya Laba Taariikhood

Sida bilaha, sannadaha sidoo kale waa la xisaabin karaa iyada oo ku xidhan haddii aad rabto in aad tiriso 1 sano sida 365 maalmood oo buuxa ama haddii isbeddelka sanadka uu xisaabiyo. Haddaba aan soo qaadanno tusaale ahaan in aan laba qaacido oo kala duwan u adeegsaday xisaabinta tirada sannadaha:

Mid ka mid ah caanaha ayaa isticmaala DATEDIF midda kalena waxay isticmaashaa shaqada YEAR. Maadaama kala duwanaanshaha tirada maalmuhu uu yahay kaliya 802, DATEDIF waxay muujinaysaa 2 sano halka shaqada SANADKA ay muujinayso 3 sano.

Mar labaad, tani waa arrin la doorbidayo waxayna ku xiran tahay waxa dhabta ah ee aad isku dayeyso inaad xisaabiso. Waa fikrad wanaagsan in la ogaado labada hab si aad u weerarto dhibaatooyin kala duwan adigoo isticmaalaya qaabab kala duwan.

Ilaa inta aad ka taxadarto qaaciidooyinka aad isticmaashid, way fududahay in si sax ah loo xisaabiyo waxaad raadinayso. Waxa kale oo jira shaqooyin badan oo taariikhi ah oo ka baxsan kuwa aan soo sheegay, markaa xor u noqo inaad hubiso kuwa sidoo kale goobta Taageerada Xafiiska^{(Office Support)} . Haddii aad wax su'aalo ah qabtid, xor u noqo inaad faallo ka bixiso. Ku raaxayso!

How to Subtract Dates in Excel

If you have an Exсel sheet with a lot of dates, chances are you wіll eventually need to calculate the differences between some of those dates. Maybe you want to see how many months it took you to pay off your debt or how many days it took you to lose a certain amount of weight?

Calculating the difference between dates in Excel is easy, but can become complicated depending on how you want to calculate certain values. For example, if you wanted to know the number of months between 2/5/2016 and 1/15/2016, should the answer be 0 or 1? Some people might say 0 since it’s not a full month between the dates and others might say 1 because it’s a different month.

In this article, I’ll show you how to calculate the differences between two dates to find the number of days, months and years with different formulas, depending on your preferences.

Days Between Two Dates

The simplest calculation we can do is to get the number of days between two dates. The good thing about calculating days is that there really is only one way to calculate the value, so you don’t have to worry about different formulas giving you different answers.

In my example above, I have two dates stored in cells A2 and A3. At the right, you can see the difference between those two dates is 802 days. In Excel, there are always multiple ways to calculate the same value and that’s what I did here. Let’s take a look at the formulas:

The first formula is just a simple subtraction of the two dates, A3 – A2. Excel knows it’s a date and simply calculates the number of days between those two dates. Easy and very straight-forward. In addition, you can also use the DAYS function.

=DAYS(A3, A2)

This function takes two arguments: the end date and the start date. If you switch the dates in the formula, you’ll just get a negative number. Lastly, you can use a function called DATEDIF, which is included in Excel from Lotus 1-2-3 days, but isn’t an officially supported formula in Excel.

=DATEDIF(A2, A3, "D")

When you type the formula, you’ll see that Excel does not give you any suggestions for the fields, etc. Luckily, you can see the syntax and all supported arguments for the DATEDIF function here.

As an added bonus, what if you wanted to calculate the number of weekdays between two dates? That’s easy enough also because we have a built-in formula:

=NETWORKDAYS(startDate,endDate)

Calculating the number of days and weekdays is simple enough, so let’s talk about months now.

Months Between Two Dates

The trickiest calculation is the number of months because of how you can either round up or round down depending on whether it’s a complete month or a partial month. Now there is a MONTHS function in Excel, but it’s very limited because it will only look at the month when calculating the difference and not the year. This means it’s only useful for calculating the difference between two months in the same year.

Since that is kind of pointless, let’s look at some formulas that will get us the correct answer. Microsoft has provided these here, but if you are too lazy to visit the link, I have provided them below also.

Round Up - =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

Round Down - =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

Now these are two fairly long and complicated formulas and you really don’t need to understand what’s going on. Here are the formulas in Excel:

Note the you should edit the round down formula in the actual cell itself because for some odd reason the entire formula does not show up in the formula bar. In order to see the formula in the cell itself, click on the Formulas tab and then click Show Formulas.

So what’s the final result of the two formulas on our current example? Let’s see below:

Round up gives me 27 months and round down gives me 26 months, which is accurate, depending on how you want to look at it. Lastly, you can also use the DATEDIF function, but it only calculates full months, so in our case the answer it returns is 26.

=DATEDIF(A2, A3, "M")

Years Between Two Dates

As with months, years can also be calculated depending on whether you want to count 1 year as a full 365 days or if a change in the year counts. So let’s take our example where I have used two different formulas for calculating the number of years:

One formula uses DATEDIF and the other uses the YEAR function. Since the difference in the number of days is only 802, DATEDIF shows 2 years whereas the YEAR function shows 3 years.

Again, this is a matter of preference and depends on what exactly you are trying to calculate. It’s a good idea to know both methods so that you can attack different problems with different formulas.

As long as you are careful with which formulas you use, it’s fairly easy to calculate exactly what you are looking for. There are also a lot more date functions outside of the ones I mentioned, so feel free to check those out also on the Office Support site. If you have any questions, feel free to comment. Enjoy!

Related posts

• 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 Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida Loo Sameeyo Liiska hubinta gudaha Excel

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Sida loo duubo Macro gudaha Excel

• Sida loola socdo isbedelada Excel

• Balacaha Saarka AutoFit iyo Dhismayaasha Saf ee Excel

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

• Sida loo isticmaalo COUNTIFS, SUMIFS, AVERAGEIFS gudaha Excel

• U qaabeeya Unugyada iyadoo la isticmaalayo Habaynta Shuruudaha ah ee Excel

• Isticmaal daaqada daawashada Excel si aad ula socoto unugyada muhiimka ah ee buug-shaqeedka

• Sida loo barbar dhigo labada faylal ee Excel oo loo muujiyo faraqa u dhexeeya

• Sida loo isticmaalo Excel AutoRecover iyo AutoBackup Features

• U isticmaal Excel Qalab ahaan si aad xogta uga koobbiso Shabakadda

• Sida loo saaro, loo jebiyo, ama loo jebiyo erayga sirta ah ee Excel XLS ee la iloobay

• Sida Loo Abuuro Liiska Qaybinta Muuqaalka