日付が多いExcelシート(Excel sheet)がある場合、最終的にはそれらの日付の差を計算する必要がある可能性があります。たぶん、あなたはあなたがあなたの借金を返済するのに何ヶ月かかったか、あるいはあなたが一定量の体重を減らすのに何日かかったかを見たいですか？
Excelで日付の差を計算するのは簡単ですが、特定の値を計算する方法によっては複雑になる可能性があります。たとえば、2016年2月5日から2016年1月15日までの月数を知りたい場合、答えは0または1にする必要がありますか？日付の間に1か月ではないため、0と言う人もいれば、別の月であるために1と言う人もいます。
この記事では、2つの日付の差を計算して、好みに応じて、さまざまな数式で日数、月数、年数を見つける方法を説明します。
2つの日付の間の日
私たちができる最も簡単な計算は、2つの日付の間の日数を取得することです。日数を計算することの良い点は、値を計算する方法が実際には1つしかないため、さまざまな数式がさまざまな答えを与えることを心配する必要がないことです。
上記の例では、セルA2とA3(A2 and A3)に2つの日付が格納されています。右側では、これら2つの日付の差が802日であることがわかります。Excelでは、同じ値を計算する方法は常に複数あり、それが私がここで行ったことです。式を見てみましょう：
最初の式は、2つの日付A3 –A2(A3 – A2)を単純に減算したものです。Excelはそれが日付であることを認識しており、これら2つの日付の間の日数を単純に計算します。簡単(Easy)で非常に簡単です。また、DAYS機能もご利用いただけます。
=DAYS(A3, A2)
この関数は、終了日(end date)と開始日(start date)の2つの引数を取ります。数式で日付を切り替えると、負の数になります。最後に、 DATEDIF(DATEDIF)と呼ばれる関数を使用できます。これは、 Lotus 1-2-3日からExcelに含まれていますが、 Excelで公式にサポートされている数式ではありません。
=DATEDIF(A2, A3, "D")
数式を入力すると、Excelからフィールドなどの候補が表示されないことがわかります。幸い、DATEDIF関数(DATEDIF function)の構文とサポートされているすべての引数をここで確認できます。
追加のボーナスとして、2つの日付の間の平日数を計算したい場合はどうなりますか？数式が組み込まれているので、これも簡単です。
=NETWORKDAYS(startDate,endDate)
日数と平日を計算するのは簡単なので、ここで月について話しましょう。
2つの日付の間の月
最も難しい計算は、月全体か一部の月かに応じて切り上げまたは切り下げを行うことができるため、月数です。現在、Excelには(Excel)MONTHS関数(MONTHS function)がありますが、差を計算するときに月のみが表示され、年は表示されないため、非常に制限されています。これは、同じ年の2か月間の差を計算する場合にのみ役立つことを意味します。
それはちょっと無意味なので、正しい答えを得るいくつかの式を見てみましょう。マイクロソフトは(Microsoft)ここ(here)でこれらを提供していますが、リンクにアクセスするのが面倒な場合は、以下にも提供します。
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)
これらは2つのかなり長くて複雑な式であり、何が起こっているのかを実際に理解する必要はありません。Excelの数式は次のとおりです。
奇妙な理由で数式全体が数式バー(formula bar)に表示されないため、実際のセル自体で切り捨てられた数式を編集する必要があることに注意してください。セル自体に数式を表示するには、[数式(Formulas)]タブをクリックしてから、[数式の表示(Show Formulas)]をクリックします。
では、現在の例の2つの式の最終結果は何でしょうか。以下を見てみましょう：
切り上げ(Round)は27か月、切り下げは26か月になります。これは、見方によって異なりますが、正確です。最後に、DATEDIF関数(DATEDIF function)を使用することもできますが、計算されるのは1か月のみであるため、この場合、返される答えは26です。
=DATEDIF(A2, A3, "M")
2つの日付の間の年
月と同様に、1年を完全な365日としてカウントするか、年の変更をカウントするかによって、年を計算することもできます。それでは、年数を計算するために2つの異なる式を使用した例を見てみましょう。
1つの数式はDATEDIFを使用し、もう1つの数式はYEAR関数(YEAR function)を使用します。日数の差はわずか802であるため、DATEDIFは2年を示しますが、YEAR関数(YEAR function)は3年を示します。
繰り返しますが、これは好みの問題であり、正確に何を計算しようとしているかによって異なります。さまざまな数式でさまざまな問題を攻撃できるように、両方の方法を知っておくことをお勧めします。
使用する数式に注意している限り、探しているものを正確に計算するのはかなり簡単です。私が言及したもの以外にもたくさんの日付機能がありますので、Officeサポート(Office Support)サイトでもそれらをチェックしてください。ご不明な点がございましたら、お気軽にコメントください。楽しみ！
How to Subtract Dates in Excel
If you have an Excel sheet with a lot of dates, chances are you will еventually need to calculate the diffеrences between some of those dates. Maybe you want to see how many months it took you to pаy off your debt or how many days it took уou tо lose a certаin amоunt 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!
