Excelの日付を求める関数について質問です。

質問

毎月20日が給与支給日で、土日祝日の場合はその前日（ex:20日が日曜→18日金曜）が支払日になるように関数で求めたい時、どのような関数を使えばよいのでしょうか。

例えばIF関数を使って、
＝IF（今月の20日が土日祝日の場合,土日祝日の前の平日,20日）
というようにする場合の（論理式）と（真の場合）の関数を教えてください。
他の関数でも大丈夫です。
PCはWindows7、Excelは2010です。

回答No.6

MarcoRossiItaly

No.3 さんが回答されている WORKDAY 関数をお勧めします。他の方法では数式が煩雑になるので。ベストアンサーは辞退します。

各年の祝日の具体的な日付については、そのリストを用意すればいいだけなので、以下は全くの余談ですが。

毎月の 20 日近辺は、最長で何連休になる可能性があるでしょうか。祝日法によれば、9/18（土）・19（日）・20（敬老の日）・21（国民の休日）・22（秋分の日）という 5 連休が最長になるようです。このとき 20 日以前の部分を見ると 18 ～ 20 の 3 日間が土・日・祝日になっていますね。

秋分の日は、地球の将来の運行状態が想定どおりとなった場合、毎年 9 月 22 日～ 24 日ごろとなる見込みです。正式には、国立天文台による計算の結果を基にして、閣議で決定されます。

このほか春分の日と海の日についても、土日を含めて 3 連休となる可能性がありますね。

回答No.5

bunjii

＝IF（今月の20日が土日祝日の場合,土日祝日の前の平日,20日）
上記の目的に合う式として次の関数式を提示します。

=MIN(IF(WEEKDAY(A2,2)<6,A2,A2+(5-WEEKDAY(A2,2))),IF(AND(COUNTIF(D2:D17,A2)>0,WEEKDAY(A2,2)=1),A2-3,A2))

条件は貼付画像を参照してください。
実質で祭日と20日が重なる可能性があるのは3月（春分の日）と9月（秋分の日）です。
MIN関数の2番目の引数が祭日と月曜日が一致した時の対応です。

回答No.4

kagakusuki

回答No.2です。

　もしも、入力する年数が西暦年ではなく、「平成〇年」等の和暦である場合には、次の様にして下さい。
　今仮に、「平成」や「昭和」といった元号がA1セルに入力されていて、B1セルには年数を、D1セルには月を表す(1～12の)数字を、それぞれ入力するものとします。
　その場合には、給与支給日を表示させるセルには、次の様な関数を入力されると良いと思います。

=IF(ISNUMBER(($A$1&$B$1&"年"&$D$1&"月1日")+0),($A$1&$B$1&"年"&$D$1&"月20日")-(WEEKDAY(($A$1&$B$1&"年"&$D$1&"月20日")+0,2)>4)*(WEEKDAY(($A$1&$B$1&"年"&$D$1&"月20日")+0,2)-5),"")

　又、もしも、年数と月が別々のセルに入力されているのではなく、１個のみのセル（例えばA1セル）にまとめて「2013年10月度」とか「平成25年10月度」等の様に入力されている場合には、次の様な関数にされると良いと思います。

=IF(ISNUMBER((SUBSTITUTE($A$1,"度",)&"1日")+FIND("年",$A$1)+FIND("月",$A$1)),(SUBSTITUTE($A$1,"度",)&"20日")-(WEEKDAY((SUBSTITUTE($A$1,"度",)&"20日")+0,2)>4)*(WEEKDAY((SUBSTITUTE($A$1,"度",)&"20日")+0,2)-5),"")

回答No.3

MackyNo1

Ａ１セルに年、Ａ２セルに月が入力されており、土日以外の会社休業日の範囲を「祝日リスト」と名前を付けている場合（範囲を絶対参照してもよい）、以下の式で20日に最も近い稼働日の日付を求めることができます。

=WORKDAY(DATE(A1,A2,21),-1,祭日リスト)

回答No.2

kagakusuki

今仮に、何年何月の給料であるのかを指定するために、A1セルには西暦年を、C1セルには月を表す(1～12の)数字を、それぞれ入力するものとします。

　その場合、給与支給日を表示させるセルには、次の様な関数を入力されると良いと思います。

=IF(ISNUMBER(($A$1&"/"&$C$1&"/1")+0),DATE($A$1,$C$1,20)-(WEEKDAY(DATE($A$1,$C$1,20),2)>4)*(WEEKDAY(DATE($A$1,$C$1,20),2)-5),"")

回答No.1

asciiz

土日はいいとして、「祝日」が困ります。
「祝日」と一口にいっても、「日本の祝日」「アメリカの祝日」「中国の祝日」など、いろいろな物が考えられるので、『Excelの関数としては用意されていない』のです。
さらに日本の場合は、振替休日やハッピーマンデーにより前後にずれる祝日も有り、「祝日テーブル」を作ったとしても毎年更新しなければなりません。

ですので、簡易的な式だけの判定は不可能です。

土日だけ判定すればいいなら、
・当日が日曜日ならば、-2日(＝18日)
・当日が土曜日ならば、-1日(＝19日)
・どちらも該当しなければ、20日そのまま
これをすればいいわけですから、weekday関数を使用します。

関数 weekday(日付シリアル値) →日曜＝1～土曜＝7の数値が返る。

仮に支給日(特定月20日)をA1セルに入れていたとすると、

=if(weekday(A1)=1, A1-2, if(weekday(A1)=7, A1-1, A1) )

このような感じになります。