エクセルで期間計算をしたい

質問

毎月1日から月末までを”1ヶ月”という扱いで
1ヶ月に満たない場合は余った日をカウントさせたいので
（例）
＜A1＞（登録日）2006/6/28
＜B1＞（脱退日）2006/9/5
と入力すると
＜C1＞（登録時日数分）※6/28～6/30・・・・3（日）
＜D1＞（登録月数）　　※7/1～8/31・・・・・・2（ヶ月）
＜E1＞（脱退時日数分）※9/1～9/5・・・・・・5（日）
という結果が出るような式を作りたいのですが
エクセルが少し分かる人にお見せしても恥ずかしくない数式で作成したく、お知恵を頂けたらありがたいです。
どうぞよろしくお願いします。

回答No.6

mshr1962 · ベストアンサー

C1=DATEDIF(A1,MIN(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20),B1)+1,"MD")
D1=DATEDIF(A1+C1,B1+1,"M")
E1=DATEDIF(MAX(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)<21),21),A1),B1+1,"MD")

回答No.7

mshr1962

#6です。済みません。式を一つ間違えました。
E1=DATEDIF(MAX(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)<21),21),A1+C1),B1+1,"MD")
が正解です。
１ヶ月以内（前月21日から当月20日の間）の場合、登録時日数分に日数が出ます。

お礼

皆様ご協力ありがとうございました。
上手く作動しそうです。
関数って奥が深いですね。
もっと勉強しなくてはと思います。

ありがとうございました。

回答No.5

poohron

作業用セルとしてA2,B2を使用しています。

A2=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>21),21)
B2=DATE(YEAR(B1),MONTH(B1)-(DAY(B1)<20),20)

C1=IF(B2>A2,A2,B1+1)-A1
D1=(MONTH(B2)-MONTH(A2))*(A2<B2)
E1=IF(B2>A2,B1,B2)-B2

ではいかがでしょうか？

A2は登録日以降の一番近い『21日』を、
B2は脱退日以前の一番近い『20日』を求めています。

一応動作確認はしたつもりですが、見落としがあるかもしれませんのでご注意下さい。

回答No.4

poohron

No.2さんの補足要求に加えて…
入会日と脱会日が同一月の場合、C1とE1の処理はどうしますか？

例えば7/1入会、7/20脱会のとき、
20日間は『登録時日数分』で処理するのか、
それとも『脱退時日数分』で処理するのか、という事です。

なお、No.1さんのご回答では正しい数値を表せなくなることがあります。
（登録日）2006/6/5、（脱退日）2006/9/5 などでお試しになると分かります。

補足

皆様夜遅くにありがとうございます。
なるほど、正しい数値が出せなくなる可能性があるということですね。。。
イメージとしては
（登録日）2006/6/5、（脱退日）2006/9/5
だった場合、
C1＝16※6/5～6/20
D1＝2※6/21～7/20、7/21～8/20
E1＝16※8/21～9/5
という結果が出るのが望ましく、

（登録日）2006/6/5、（脱退日）2006/6/20
だった場合は
C1＝16
D1＝0
E1＝0
という結果が出るのが望ましいです。

日付の情報から年、月、日の情報を抽出してあれこれ試してみたのですが式が幼稚で無駄な参照セルが多いのでなんとかスマートな式で完成させたいと試行錯誤しているところでした。

どうぞよろしくお願いします。

回答No.3

noname#262398

C1=IF(DAY(A1)=1,0,DATE(YEAR(A1),MONTH(A1)+1,1)-A1)
D1=MONTH(B1)-MONTH(A1)-1+(C1=0)+(E1=0)
E1=IF(DAY(B1+1)=1,0,B1-DATE(YEAR(B1),MONTH(B1),0))

でしょうか？

回答No.2

web2525

入会日が月初（１日）の場合
退会日が月末の場合

Ｃ１とＥ１の処理はどうしますか？

例）７／１入会、８／３１退会の場合

お礼

皆様や分にありがとうございます。
C1とE1は余りの日が無いので0で表示で構いません

NO.1の方の補足で書き込みましたが
一ヶ月の期間が月初から月末ではなく
21日から20日の場合を想定して数字を出せたら
とても助かります。
よろしくお願いします。

回答No.1

hisa-gi

動作確認は行ってないのですが、下記のではどうでしょうか？

C1 =DATEDIF(A1,DATE(YEAR(A1),MONTH(A1)+1,1),"D")
D1 =DATEDIF(A1,B1,"M")
E1 =DATEDIF(DATE(YEAR(B1),MONTH(B1),0),B1,"D")

補足

ご回答ありがとうございました！
完璧に思い通りの日付が出ました。
実はこの件友人に表の作成を頼まれたので早速報告した時に分かったのですが、
一ヶ月の期間が1日から月末ではなくて
21日から20日〆なのだそうです。。。
式の作り直しをしたいところなのですが、あいにく
DATEDIFという関数の論理が分からないので教えて頂けたらありがたいです。夜分に恐れ入ります。
どうぞよろしくお願いします。