エクセルの計算式

次のような場合の計算式を教えて下さい。

日数の計算です。

１、２０１１／０３／１５～２０１１／５／１０　　
　　※ここの１ヶ月と２５日に計算式はわかります。

但し次のような場合　
２，２０１１／０６／２０～２０１２／０９／１０　　
　　※ここの１年の計算式は分かりますが、月数が１４ヶ月となります。これを１年２ヶ月２１日と計算する計算式を教えて下さい。

また、
１，２ヶ月１５日
２，３ヶ月１０日
３，１年２ヶ月２１日　の期間があった場合　
これを合計して　１年８ヶ月２６日　と上記期間毎とそれを合計した合計期間を計算する方法を教えて下さい。
よろしくお願いします。

No.4 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2013/10/12 17:01

翌日起算で計算する一例。

複雑でだいぶ難しいですが、一応、下の数式で計算できます。なおバグを含むとされている DATEDIF 関数の使用は避けてみました。

A2　2012/2/2
B2　2014/2/1
C2　=b2-a2
D2　=int(c2/365)-(date(year(b2),month(a2),day(a2))>b2)
E2　=mod(month(b2)+12*(month(a2)>month(b2))-month(a2)-(day(a2)>day(b2)),12)
F2　=b2-date(year(b2),month(b2)-(day(a2)>day(b2)),day(a2))
G2　=d2&" 年 "&e2&" か月 "&f2&" 日"
A4　合計
C4　=sum(c2:c3)
D4　=int(c4/365.25)
E4　=int((c4-365.25*d4)/(365.25/12))
F4　=round(c4-365.25*d4-365.25/12*e4,)

この回答への補足

大変ありがとうございます。

少しエラーがあるので教えて下さい。

開始日　２０１３／５／６　終了日　２０１３／６／１３　とすると
Ｆ２　「日」の表示が　１９００／１／７　と表示されます。　
本当であれば　７　と表示されると思うのですが・・
これは　Ｆ３も部分も同じです。（下記　開始・終了日を入力　１９００／１／２７と表示されます。）

それと
開始日　２０１２／１２／８　終了日　２０１３／４／４　とすると
Ｄ３　「年」に－１と表示されます。
１年は経過していないので、０年３ヶ月２７日　と表示でいないでしょうか？

以上　２点　ご教授いただければ大変助かります。
大変わかりやすのでこの方法を使いたいのでよろしくお願いいたします。
　

補足日時：2013/10/12 18:35

No.7 回答者： bunjii 回答日時： 2013/10/13 12:23

期間の計算は基準が異なると正しい結果を得られません。

>１、２０１１／０３／１５～２０１１／５／１０
あなたの計算では「１ヶ月と２５日」と言うことですが1ヶ月を30日とすれば3月31日の分が誤差になります。
また、起算日の「2011/03/15」を算入するか否かでも1日の誤差が生じます。
保険契約等の場合は契約日も含んでいますがあなたの見解は如何でしょうか？

>２，２０１１／０６／２０～２０１２／０９／１０
あなたの判定で「１年２ヶ月２１日」とされる根拠も提示しなければ正しい計算式は導けません。

暦を基準とする計算と経過日数から換算する方法では手順が異なりますので厳密に提示してください。
尚、Excelでの日時を扱うときは実際の値（シリアル値）と表示が異なることが起こります。
シリアル値の1は年月日の起点である1900年1月1日に置き換えて表示されます。
閏年は2月29日が自動的に含まれます。
これらのことを念頭に質問の内容を見直してください。

No.6 回答者： MarcoRossiItaly 回答日時： 2013/10/13 01:12

まずは、要請にお答えします。

＞あと１つだけお伺いしたいことがあります。
　……
　期間計　１年１３ヶ月３６日
　　　　　　　　　　↓
　よって　　　２年２ヶ月６日　となるようにします。
　※日数を３０日で計算します。（このような日数の算出方法が民法の規定にあるそうです。）


要するに、30 日ごとに繰り上げたいということですね。それは単に、日数を 30 日で割り算して、商と余りを求めるだけですね。12 か月の日数は、30 日/月 x 12 月 = 360 日とすればいいですね。

D4　=INT(C4/(30*12))
E4　=INT((C4-30*12*D4)/30)
F4　=C4-30*(12*D4+E4)

なお 1 か月の長さはご存じのとおり 28 ～ 31 日まで幅があるわけですから、シート各行の○年△月×日という答えの○、△、×をそれぞれ合計した値と、日数の合計を○年△月×日の形式に直した場合の値は、一般には一致しないはずです。各年・各月の繰り上がりのリズムが一定していないので。



＞※日数を３０日で計算します。（このような日数の算出方法が民法の規定にあるそうです。）


本当にそれで合っているか、よくよくご確認ください。例えば参考 URL のページなどでは、大の月／小の月によって実日数が変わると述べている民法上の事例もあります。

また、契約期間その他において「30 日」と言うのと「1 か月」と言うのでは、扱いが異なっているはずなので、ご注意ください。「10 月 31 日」から 1 か月だと、翌月の「応当する日」（応当日）が「11 月 31 日」であって存在しない日となるので、「その前日」11 月 30 日をもって期間が満了、といった計算の仕方が 143 条ではされたりしますね。

参考URL：http://www5d.biglobe.ne.jp/Jusl/TomoLaw/KikanKei …

No.5 回答者： MarcoRossiItaly 回答日時： 2013/10/12 22:12

No.4 です。

失礼しました。ご指摘のあった 2 点のうち、1 点は数式の誤りです。もう 1 点は誤りではなく、正常な動きです。


＞開始日　２０１２／１２／８　終了日　２０１３／４／４　とするとＤ３　「年」に－１と表示されます。


失礼しました。誤りなので、次式に差替えをお願いいたします。

D2　=year(b2)-year(a2)-(date(year(b2),month(a2),day(a2))>b2)


＞開始日　２０１３／５／６　終了日　２０１３／６／１３　とするとＦ２　「日」の表示が　１９００／１／７　と表示されます。
　本当であれば　７　と表示されると思うのですが・・


その現象は、操作の手順によっては Excel が勝手に、D ～ F 列のセルの書式として A ～ B 列（参照元）と同じものを自動的に設定してしまうために起こります。現在、D ～ F 列の書式を確認すると日付関係のものになっていますので、「数値」などの書式に変更すれば、「1900/1/7」が「7」に変化します。なおこの変化は見かけだけの問題であって、セルに記入されている値や数式そのものには、何の変化も起きていません。

この回答への補足

本当にありがとうございます。

あと１つだけお伺いしたいことがあります。

次のような条件があります。
期間１　１年２ヶ月１０日
期間２　　 　７ヶ月１４日
期間３　　 　４ヶ月１２日
期間計　１年１３ヶ月３６日
　　　　　　　　　　↓
よって　　　２年２ヶ月６日　となるようにします。
※日数を３０日で計算します。（このような日数の算出方法が民法の規定にあるそうです。）


そこで、Ｆ２の７日とＦ３の２７日を合計すると１ヶ月４日とならなければなりませんが、現在の計算式だと　１ヶ月３日となってしまいます。
そのため　G２　０年１ヶ月７日と　G３　０年３ヶ月２７日　の　合計　G４が　０年５ヶ月３日となっています。
この　G4を　０年５ヶ月４日とする方法はないでしょうか？

最初に記載すれば良かったのですが、要領を得ない質問で迷惑をお掛けしますが、よろしくお願いします。

補足日時：2013/10/12 23:10

No.3 回答者： keithin 回答日時： 2013/10/12 16:08

区間の日数では３０日は使わずに、合計のとこだけを３０日１か月で計算したい混在ルールということですね。

（あんまり感心できませんが、そうしたいなら。）


＞申し訳ございませんが具体的な計算式で教えて下さい。

では、アナタのエクセルに具体的にどこに何を記入してるのかから説明が無いとダメです。

簡単のため
＞=DATEDIF(B4,B5,"Y")&"年"&DATEDIF(B4,B5,"YM")&"ヶ月"&DATEDIF(B4,B5,"MD")&"日"

この数式を
=DATEDIF(B4,B5,"Y")*10000+DATEDIF(B4,B5,"YM")*100+DATEDIF(B4,B5,"MD")
に変更して、セルの書式設定の表示形式のユーザー定義で
0年00ヶ月00日
と設定しておきます。
これがＢ６にあります。

また「１年２か月２１日」改め「1年02ヶ月21日」がＣ６にあります。

合計の式は

=INT(SUMPRODUCT(INT(B6:C6/10000)*360+MOD(INT(B6:C6/100),100)*30+MOD(B6:C6,100))/360)*10000
+INT(MOD(SUMPRODUCT(INT(B6:C6/10000)*360+MOD(INT(B6:C6/100),100)*30+MOD(B6:C6,100)),360)/30)*100
+MOD(SUMPRODUCT(INT(B6:C6/10000)*360+MOD(INT(B6:C6/100),100)*30+MOD(B6:C6,100)),30)
と記入し、同じく0年00ヶ月00日の表示形式を付けておきます。

この回答へのお礼

大変参考になりました。ありがとうございました。なんとか解決できそうです。

お礼日時：2013/10/13 15:52

No.2 回答者： keithin 回答日時： 2013/10/12 12:48

＞これを１年２ヶ月２１日と計算する計算式

Ａ１からＢ１の日付で
=TEXT(DATEDIF(A1,B1,"y"),"0年;;;")&TEXT(DATEDIF(A1,B1,"ym"),"0ヶ月;;;")&TEXT(DATEDIF(A1,B1,"md"),"0日;;;")
といった具合に文字列で計算結果を表示します。

文字列で結果を出す方式にしとかないと、たとえば
2013/6/20から
2013/9/19まで
みたいな計算ができません。



ここから派生する問題として、説明されていませんが後半のご質問は「３０日で１か月とする」という前提を置いて計算したいという事でしょうか。（上述の回答は、こちらで例示した6/20～9/19のようにそういう前提ではありません）

それならそれで、区間の日数を「日単位」ですべて計算（=B1-A1、あるいは２か月×３０＋１５日）し、合計日数を改めて３０日・１２か月×30日で繰り上げていく(MOD関数を使う）ようなのも方法の一つになります。

また別のアプローチとしては、
Ａ１，Ｂ１に日付、Ｃ１に２か月１５日
Ａ２，Ｂ２に日付、Ｃ２に３か月１０日
Ａ３，Ｂ３に日付、Ｃ３に１年２か月２１日
としたときに、前述の数式を使い「SUM(A1:A3)からSUM(B1:B3)まで」で計算するという方法もあります。

この回答への補足

できましたら追加して計算式をお願いします。

ご指摘の通り「３０日で１か月とする」で計算しています。（質問の要領が悪く申し訳ありません。）

=DATEDIF(B4,B5,"Y")&"年"&DATEDIF(B4,B5,"YM")&"ヶ月"&DATEDIF(B4,B5,"MD")&"日"

この計算式で、
１，２０１２／３／１５から２０１２／５／１０　を０年１ヶ月２５日
２，２０１２／６／２０から２０１３／９／１０　を１年２ヶ月２１日

以上で項目別に期間が計算できた後

０年１ヶ月２５日　＋　１年２ヶ月２１日　
　　　　　 のように足して
　　　　「１年４ヶ月１６日」

と計算結果として表示できる計算式を教えて下さい。

見よう見まねでやっているので、申し訳ございませんが具体的な計算式で教えて下さい。
よろしくお願いします。　

補足日時：2013/10/12 14:55

No.1 回答者： ohinasama144 回答日時： 2013/10/12 12:34

DATEDIF関数

を使えば出来ると思います。

http://kokoro.kir.jp/excel/datedif.html

参考URL：http://kokoro.kir.jp/excel/datedif.html