エクセルで銀行の毎月の返済日を土日祭日を除く方法を・・・

質問

エクセルで毎月の返済日が、土日祭日の場合翌営業日が返済日となるが、これを関数を利用して表示されるようにしたい。

例えば、毎月25日の場合、今年の11月25日は日曜日のため翌日の26日に。。。
末日返済の場合には9月であれば翌営業日の10月1日になります。

エクセルで＝EDATE関数　あるいはEOMANTH関数を利用して返済日を表示していますが、前述のように翌営業日になる場合、or翌月になる場合の関数の式がよく分かりませんが、分かる方、どなたかよろしくお願いします。。。。。　　<m(__)m>

回答No.3

zap35 · ベストアンサー

＃０１です。毎月同日は２５日ではなく１５日なのですか？
私のＰＣで計算した結果です。祝日テーブルを絶対参照に改めました。
Ａ列の年月は日付形式で入力し、書式で　yyyy/m　にしています。
Ａ列　　　　Ｂ列　　　　　　　　Ｃ列　　　　　　　　Ｆ列
2007/9　　2007/9/18　　　 2007/10/1　　　 2007/9/17
2007/10　 2007/10/15　　　2007/10/31　　　2007/9/24
2007/11　 2007/11/15　　　2007/11/30　　　2007/10/8
2007/12　 2007/12/17　　　2008/1/4　　　　2007/11/3
2008/1　　2008/1/15　　　 2008/1/31　　　 2007/11/23
2008/2　　2008/2/15　　　 2008/2/29　　　 2007/12/23
　　　　　　　　　　　　　　　　　　　　　　　　　　　2007/12/29
　　　　　　　　　　　　　　　　　　　　　　　　　　　2007/12/30
　　　　　　　　　　　　　　　　　　　　　　　　　　　2007/12/31
　　　　　　　　　　　　　　　　　　　　　　　　　　　2008/1/1
　　　　　　　　　　　　　　　　　　　　　　　　　　　2008/1/2
　　　　　　　　　　　　　　　　　　　　　　　　　　　2008/1/3
　　　　　　　　　　　　　　　　　　　　　　　　　　　2008/1/14
　　　　　　　　　　　　　　　　　　　　　　　　　　　2008/2/11
Ｂ1セルの式
=WORKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),15),-1,$F$1:$F$14),1,$F$1:$F$14)

Ｃ１セルの式
=WORKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1,$F$1:$F$14),1,$F$1:$F$14)

お礼

うまくいきました、ブラボーです＼(^o^)／。。。
15日の件は、スミマセンでした、あくまで例として25日としたまでで、
今日が9/15日でしたので、そのまま15日としたものでした。。。
<m(__)m>

Ａ列に前もって年月を入れておくのですね～、全く気づきませんでした。。
これで長い間悩んでいた事が解決できました。
マニュアル本にはまったくこれらの件は載っていないですよね～

大変助かりました。ありがとうございます。

回答No.2

zap35

＃０１です。WORKDAY関数の使い方が分かれば応用できると思うのですが、例えばこんな方法はどうですか
A1に　2007/9　と入力して、下方向にコピーします。表示は　Sep-07　のような表記になるかもしれませんが、これはセルの書式で、お好きなように変更してください

B1には25日の返済日の式
　=WORKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),25),-1,F1:F7),1,F1:F7)

C1には月末の返済日の式
　=WORKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),-1,F1:F7),1,F1:F7)

をそれぞれ貼り付けて、下方向にコピーします

お礼

おはようございます。
今朝やってみましたが・・・・・？？

それぞれ貼り付けて、下方向にコピーしてみましたが、
列には同じ日付だけが連続して表示されました。。。
＃０１のＰＣでは、下記のようにキチンと毎月の日付が下方向に表示されたのですよね～
　　毎月同日 　毎月末
2007/09/15(土) 2007/09/15(土)
2007/10/15(月) 2007/09/30(日)
2007/11/15(木) 2007/10/31(水)
2007/12/15(土) 2007/11/30(金)
2008/01/15(火) 2007/12/31(月)

私のＰＣ、エクセルがおかしいのかな～？？

回答No.1

zap35

WORKDAY関数を使用します。ただしこの関数は「ツール」→「アドイン」→「分析ツール」にチェックをつけておく必要があります

毎月２５日が返済日の場合、今月の返済日は以下の式で求めることができます。
　=WORKDAY(WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),25),-1,F1:F7),1,F1:F7)
ただし、この式でF1:F7は祝日や年末年始休日(2007/9/17　など)のリストを入力したセル範囲です。

２５日が休業日のことを考えて、２５日の１日前の営業日を求め、さらにその１日後の営業日を求める式にしてあります

次に末日のほうですが、まず今月の月末日は以下の式で求められます
　=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
後は２５日の式と同じように
　=WORKDAY(WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),-1,F1:F7),1,F1:F7)
で求めることができます。末日が休日の場合は翌月の最初の営業日が結果として返ります

補足

早速のご返事ありがとうございます。

すみません、ちょっと説明不足でした。。。m(__)m
毎月の返済予定日を例えば、3年分、5年分を作成をしたかったのです。
教えていただきました関数を列に連続してコピペしましたが、始めのセルからエラーが出てしまいました。勿論Ｆ列には祭日を２０１０年まで入力済みです。。
よろしくお願いします。