Excelの様々な関数の読み方や引数などを紹介。今回は、WORKDAY関数～WRAPROWS関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、WORKDAY関数～WRAPROWS関数です。

＜Excel関数辞典：VOL.90＞

今回は、WORKDAY関数～WRAPROWS関数までをご紹介しております。

WORKDAY関数

読み方： ワークデイ　　

分類： 日付時刻 

WORKDAY(開始日,日数,[祭日])

稼働日数後の日付を算出します　

WORKDAY.INTL関数

読み方： ワークデイ・インターナショナル　　

分類： 日付時刻 

WORKDAY.INTL(開始日,日数,[週末],[祭日])

週末（曜日指定OK)と祝日を除いた日数後の日付を算出します　

WRAPCOLS関数

読み方： ラップコルズ　　

読み方： ラップカラムズ

分類： 検索/行列 

WRAPCOLS(vector,wrap_count,[pad_with])

指定した数の値の後に列で折り返（ラップ）します　

WRAPROWS関数

読み方： ラップロウズ　　

分類： 検索/行列 

WRAPROWS(vector,wrap_count,[pad_with])

指定した数の値の後に行で折り返（ラップ）します

Excel。10日締め翌10日払いの日が、土日祝日なら前営業日にするにはどうするの【before the weekend】

Excel。10日締め翌10日払いの日が、土日祝日なら前営業日にするにはどうするの

＜WORKDAY＋EOMONTH＋IF＋DAY関数＞

支払予定日が、土日祝日だと、だいたい、前営業日に支払いをすることになるわけです。

その前営業日を算出するには、どのように関数を組み合わせたたらいいのでしょうか。

次のケースをつかって説明します。

結論というか、対応した数式をまず、紹介すると、

=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)

という数式をつくることで、土日祝日ならば、前営業日を算出することができます。

A2の2023/10/3は、10日締めなので、翌月の10日が支払日です。

B2のように2023/11/10が支払日となります。

B2に設定した数式は、

=EOMONTH(A2,IF(DAY(A2)<=10,0,1))+10

この式がベースとなっていきます。

EOMONTH関数は、月末日を算出する関数です。

1番目の引数は、「開始日」なので、A2を設定します。

これで、2023/10/31が月末日です。

2番目の引数は、「月」です。

10日締めなので、当月なのか、翌月の月末なのかという月を算出させる必要があります。

IF(DAY(A6)<=10,0,1))

IF関数とDAY関数をつかって、10日以前なのどうかを判断させています。

10日以前ならば「0」。

そうでなければ「1」とすることで、当月末なのか、翌月末なのかを算出できます。

その日付に「+10」すれば、10日払いの日付を算出することができるというわけです。

もし、25日払いならば、「+25」とすればいいわけです。

だから、EDATE関数ではなくて、EOMONTH関数をつかったというわけです。

さて、算出した日付が、平日ならばいいのですが、土日祝日だった場合、金融機関がお休みなので、前営業日にしたいわけです。

そこで、土日祝日を除くことができるWORKDAY関数をつかって、先程のEOMONTH関数をネストします。

=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)

WORKDAY関数の最初の引数は、「開始日」です。

これは、先程のEOMONTH関数で算出した数式を設定します。

ただし、先程、10日払いだから「+10」としましたが、前営業日にしたいので、ワザと1日多い「+11」にします。

2つ目の引数は、「日数」なので、これを「-1」と設定することで、前営業日を算出することができます。

この「+1」と「-1」の考え方ですが、前営業日にしたいので「-1」したいわけです。

土曜日の場合は、「-1」すれば金曜日なので、問題はないのですが、金曜日など平日の場合は、その日でいいのにもかかわらず、「-1」されてしまい、前日が支払日として算出されます。

そのため、わざと「+11」と一日多くして、「-1」させるという方法をつかっております。

最後3つ目の引数は、祝日のカレンダーを用意しておき、その日付にも対応するようにしています。

土日祝日が絡んだ場合、前営業日を算出する方法をご紹介しました。

Excel。土日祝日を除いた予定表を手早く作りたいけど、どうしたらいい。【calendar】

Excel。土日祝日を除いた予定表を手早く作りたいけど、どうしたらいい。

＜WORKDAY関数＞

予定表を作るときに、日付を設定するわけですが、土日や祝日を除いた予定表を作りたいとしたら、どのようにしたらいいのでしょうか。

次の表があります。

A列に日付を入力しています。

土日祝日も含めたカレンダーになっているものを平日のみのカレンダーにしたいわけですね。

目視で確認して自力で削除することが多いと思いますが、作業自体は単純でも面倒です。

かといって、Excel VBAでつくるというのも面倒です。

このような場合、登場するのが「WORKDAY関数」です。

そして、このWORKDAY関数を作るとき、重要になるのが、E2:F5のような祝日一覧表です。

祝日を自動的にExcel側で判断することができないので、用意する必要があります。

では、土日祝日を除いた予定表を作っていきましょう。

A3の最小の日付は、そのまま「2022/5/1」と入力しています。

別のセルに年月日を用意しておいて、DATE関数で日付を作るというのもいいですね。

A4にWORKDAY関数で数式を設定します。

=WORKDAY(A3,1,$E$3:$E$5)

あとは、オートフィルで数式をコピーするだけです。

すると、土日祝日を除いた予定表。カレンダーをつくることができました。

では、=WORKDAY(A3,1,$E$3:$E$5)の引数を確認しておきましょう。

最初の引数は、開始日なので、最初の日付である、A3を設定します。

次の引数は、日数。開始日から「+1」すれば翌日になりますので、「1」と設定します。

最後の引数は、祭日。

これは祝日一覧の日付を設定しますので、「$E$3:$E$5」。

オートフィルで数式をコピーすることを考慮する必要があるので、絶対参照を忘れずに設定します。

なお、B列の曜日ですが、B2には、

=TEXT(A3,"aaa")

とTEXT関数をつかって、表示形式を日付から曜日に変更しています。

WEEKDAY関数をつかった曜日算出でもOKですし、セル参照して表示形式で変更してもOKですね。

また、土日ではなくて、水曜日など別の曜日の場合には、「WORKDAY.INTL関数」をつかうことで、対応することが可能です。

最後に運用上のポイントなのですが、日本の祝日は、俗にいうラッキーマンデーがあるので、祝日が固定されていない祝日が多くあります。

年がわかる・年度がかわるに連動して、この一覧を修正する必要があります。

年や年度をまたぐことが想定される場合には、月日での管理よりも、年月日での管理運用をおススメします。

日付関係の関数も色々ありますので、確認してみると作業効率を改善できる関数を見つけることが出来るかもしれませんね。

Excel。今日から土日と火曜を除いた10日後の日付を算出したい【WORKDAY.INTL】

Excel。今日から土日と火曜を除いた10日後の日付を算出したい

＜WORKDAY関数・WORKDAY.INTL関数＞

見積書などの有効期限を、単純に今日から10日後というのであれば、日付に「+10」すれば簡単に算出することができますが、今日から10日後なんだけど、土日と火曜日を除いた日付を算出したい場合は、どのようにしたらいいのでしょうか？

ということで、今回は、

「土日を除く10日後」
「火曜日を除く10日後」
「土日と火曜日を除く10日後」

を算出していきます。

【土日・祝日を除くなら、WORKDAY関数】

土日・祝日を除いた日を算出するだけならば、WORKDAY関数を使えば簡単に算出することができます。

カレンダーで確認してみると、2日から10日後なので、12月16日月曜日を算出するはずです。

では、B2をクリックして、WORKDAY関数ダイアログボックスを表示します。

開始日は、A2。今回は、2019年12月2日月曜日です。
日数は、10日後なので、「10」と設定します。

今回は、祝日の設定は除きますので、OKボタンをクリックします。

算出されたようですが、シリアル値で算出されてしまいましたので、書式のコピーを使って、A2の書式をB2に書式をコピーしましょう。

カレンダーを使って確認したように、12月16日を算出することができました。

【土日ではなく火曜日を除くなら、WORKDAY.INTL関数】

土日ではなくて、火曜日を除くならどうしたらいいのでしょうか？

そこで、登場するのが「WORKDAY.INTL関数」このINTLは、インターナショナルと読みます。

このWORKDAY.INTL関数は、ダイアログボックスを使うよりも、手入力で設定するほうが楽だと思いますので、今回は、手入力で作っていきます。

除外したい曜日の番号を選択します。火曜日のみ除外したいので、13を設定します。

C2の数式は、
=WORKDAY.INTL(A2,10,13)

シリアル値で算出されますので、書式をコピーして確認すると、12月14日を算出します。念のため、カレンダーで確認してみましょう。

火曜日を除くと、10日後は、確かに14日で間違いありません。

それでは、土日と火曜日を除く場合どのようにしたらいいのでしょうか？

【月曜から日曜を0と1で設定する】

火曜日を除く場合は、WORKDAY.INTL関数では、引数の「週末」を13とすることで、算出することができましたが、土日を加えるにはどうしたらいいのでしょうか？

引数の「週末」は、月曜から日曜までを0と1をつかった７ケタの数値で、除外するか否かを設定することができるのです。

カレンダーで確認すると、12月19日と算出されればいいようです。

使う関数は、先程と同じ、WORKDAY.INTL関数です。

D2に次の数式を設定します。
=WORKDAY.INTL(A2,10,"0100011")

シリアル値で算出されますので、書式をコピーして、確認してみると、12月19日と算出されたことが確認できます。

さて、引数にある、"0100011"は何を意味にしているのかというと、月曜日から日曜日までを該当するなら1。
該当しないなら0と表現した数値です。

月 火 水 木 金 土 日
0 1 0 0 0 1 1

この方法を使うことで、水曜日から金曜日まで除いてなど、様々なパターンに対応することができます。

Excel。土日祝日を除いた前日・後日を算出する方法。2019年の大連休対策【holidays】

Excel。土日祝日を除いた前日・後日を算出する方法。2019年の大連休対策

＜WORKDAY関数＞

2019年は平成31年から新元号へ変わったりしますが、事務職や経理さんが頭をかかえるのが、4月29日からの大連休にともなう、振込日。

前日なのか後日なのか？2019年は、大連休以外にも振替休日が発生したりしますので、改めて日付を確認する方法を押さえておきましょう。

【祝日一覧を作成する】

最初に用意しないといけないのは、祝日一覧表。

2019年は、12月に祝日がないんですね。

月初・月末と25日あたりに振込日があることが多いので、今回は、4月30日に振込予定日がある場合を例として日付を求めていくことにしましょう。

なお、この表のA列ですが、表示形式のユーザー定義を使って、yyyy/m/d(aaa)と設定しているので、日付の後ろに曜日が表示しております。

※入力するのが面倒な場合は、コピーして直接Excelに貼り付けて、フラッシュフィルを使ったりして、一覧表を作ってみてください。

日付 祝日名
2019/1/1 元旦
2019/1/14 成人の日
2019/2/11 建国記念の日
2019/3/21 春分の日
2019/4/29 昭和の日
2019/4/30 祝日
2019/5/1 即位の礼
2019/5/2 祝日
2019/5/3 憲法記念日
2019/5/4 みどりの日
2019/5/5 こどもの日
2019/5/6 振替休日
2019/7/15 海の日
2019/8/11 山の日
2019/8/12 振替休日
2019/9/16 敬老の日
2019/9/23 秋分の日
2019/10/14 体育の日
2019/11/3 文化の日
2019/11/4 振替休日

【WORKDAY関数を使って振込日を算出しましょう】

次のような表を用意します。

今回はわかりやすいようにするために、2月3月4月で見てみましょう。

土曜日曜と祝日を除くには、WORKDAY関数を使うことで求めることができますが、ちょっと考える必要があります。

ではF2をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。

開始日は、E2+1と入力します。
日数は、-1と入力します。
祭日は、$A$2:$A$21。オートフィルで数式をコピーしますので、絶対参照を設定しています。

F2の数式は、
=WORKDAY(E2+1,-1,$A$2:$A$21)
では、オートフィルで数式をコピーしましょう。

4月末日は、繰り上がって繰り上がって、4月26日になっちゃうんですね。ほとんど、25日と変わらんでしょう！こりゃ～各担当者さん。業務が立て込んじゃうよね。

ところで、なんで、+1したり、-1したりしているのでしょうか？
関数の動きを確認しておきましょう。

2月28日に+1すると3月1日になってしまいますが、日数で-1（マイナス1）しますので、2月28日になります。
この2月28日は、A2:A21のデータと該当しないので、2月28日と算出されます。

3月31日は+1すると、4月1日月曜日になるのですが、日数で-1すると、3月31日日曜日になり、WORKDAY関数は、土日を除外するので、4月1日月曜日の土日を除いた-1。
すなわち3月29日金曜日を算出します。

ということで、4月30日に+1すると5月1日になるのですが、5月1日は、『即位の礼』でA2:A21にデータが該当するために、土日祝日を除いて-1した、4月26日金曜日と算出されるわけですね。

では、土日祝日を除いた翌日だった、どのような計算式を作ればいいのでしょうか？

G2をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。

開始日は、E2-1
日数は1
祭日は$A$2:$A$21
G2の数式は、
=WORKDAY(E2-1,1,$A$2:$A$21)
それでは、オートフィルで数式をコピーして確認してみましょう。

3月31日で動きを確認してみましょう。

3月31日日曜日の-1なので、3月30日土曜日ですが、土日を除いた日数+1なので、4月1日月曜日と算出されました。

4月30日火曜日は、祝日なので、-1しても、4月29日月曜日。A2:A21のデータと該当するので、日数+1するのですが、5月1日【即位の礼】から5月6日月曜日の「こどもの日の振替休日」まで祝日のデータと合致してしまうので、5月7日火曜日が算出されています。

ということで、今年2019年は、カレンダーをきちんと確認しておかないと、銀行窓口やATM激混なんてことが多発しかねませんので、注意が必要なのかもしれませんね。

Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法【WORKDAY】

Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法

＜DATE関数+WORKDAY関数　EOMONTH関数＞

新年を迎えると日付関係で確認しておかないことが発生する、
ビジネスマンも多いですよね。

例えば、月末締めの翌々10日払いとか振込日など関連する日があるとは思います。
この支払日や振込日が平日ならば問題はないのですが、
土日祝日に絡んだりすると、とても厄介なんですね。

土日祝日の前に振込しておかなければいけないのか？土日祝日の後でいいのか？

資金繰りに絡んできますので、しっかり把握しておきたいところ。

そこで、今回は、2018年の祝日を確認しつつ、土日祝日を除いた前日、
あるいは後日を求める方法をご紹介してきます。

まず、次の表を用意します。

A1には、2018と入力して、表示形式で単純に、G/標準"年"として、
2018年と表示できるようにしております。

A4:A9も同じように、G/標準"月"として、表示上、1月としてあります。

このあと、これらの数値をDATE関数で日付を求めるために使うので、
数値でないとマズイわけです。

月末の列ですが、
B4には、次の数式を設定してあります。

月末を算出する関数の、EOMONTH関数を使って、
それぞれの月末を算出しております。

開始日には、DATE($A$1,$A4,1)
DATE関数は、日付を作ることができる関数ですね。
このために、先ほど表示形式を使っていたわけですね。

月には、0（ゼロ）。今月末を知りたいので、0（ゼロ）ですね。

あとは、OKボタンをクリックして、オートフィルを使って数式をコピーしましょう。

B4の数式は、
=EOMONTH(DATE($A$1,$A4,1),0)

すると、このようになりましたね。

日付ではなくて、シリアル値で算出されてきましたので、
これも表示形式を使って日付にしましょう。

また今回は、曜日が絡みますので、曜日も表示できるように変更しましょう。

表示形式を、ユーザー定義を使って、yyyy/m/d(aaa)としてみましょう。
曜日月の日付が表示されましたね。

それでは、早速…といきたいところですが、
もっとも大切なデータを事前に作っておく必要があります。

次の表を確認してみましょう。

祝日の一覧表が必要になるのです。

これがないと、Excelが祝日かどうかの判断ができないのです。

作るのは大変だと思いますので、次の行をコピーして使ってください。
祝日 内容
2018/1/1(月) 元旦
2018/1/8(月) 成人の日
2018/2/11(日) 建国記念の日
2018/2/12(月) 建国記念の日の振り替え
2018/3/21(水) 春分の日
2018/4/29(日) 昭和の日
2018/4/30(月) 昭和の日の振り替え
2018/5/3(木) 憲法記念日
2018/5/4(金) みどりの日
2018/5/5(土) こどもの日
2018/7/16(月) 海の日
2018/8/11(土) 山の日
2018/9/17(月) 敬老の日
2018/9/23(日) 秋分の日
2018/9/24(月) 秋分の日の振り替え
2018/10/8(月) 体育の日
2018/11/3(土) 文化の日
2018/11/23(金) 勤労感謝の日
2018/12/23(日) 天皇誕生日
2018/12/24(月) 天皇誕生日の振り替え

先に結果を見てみましょう。
月末の前日か後日かを算出してみるとこのようになります。

4月30日は昭和の日の振り替え休日なので、
祝日前・祝日後ともに日が変わっていますよね。

このように、土日祝日を除くことができる関数。
それが、【WORKDAY関数】です。

WORKDAY関数を使って、C4の数式を作っていきましょう。

C4をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。

開始日は、B4+1
日数には、-1
祭日は、$F$4:$F$23
あとは、OKボタンをクリックしてオートフィルで数式をコピーします。

なぜ、開始日で+1して、日数で-1するのかというと、
このWORKDAY関数なのですが日数の引数を0（ゼロ）にすることができないのです。

確認してみるとわかるのですが、

3月31日が土曜日なので、C6の数式を開始日に+1しないで、
日数を0にしてみると、3月31日が算出されてしまうのです。

つまり、日数が0だと土日でも、リアクションしないのです。

なので、+1して-1するという方法を使っています。

それでは、C4の数式は、
=WORKDAY(B4+1,-1,$F$4:$F$23)

同じように、D4の数式を確認しておきましょう。
=WORKDAY(B4-1,1,$F$4:$F$23)
こちらは、-1してから、+1するようにしております。

このように、WORKDAY関数を使うと、
土日祝日を除けることができますが、まずは、祝日一覧表を作ってからですね。

Excel。paymentday。10日締めの翌10日後払い。日程表は今までのスキル総決算！

Excel。paymentday。10日締めの翌10日後払い。
日程表は今までのスキル総決算！

WORKDAY関数とDATE関数とEOMONTH関数

現場といいますか、会社の取引や支払は様々なケースがありますよね。
前回まではまだ支払までの日数があったりしましたが、
まだ取引先様とのやり取りが少ない等々、支払い日数が短いパターンというのがありまして、
今回は、土日祝祭日に完全対応した支払予定日の日程表を
作成してみようというシリーズの最終回。

今回は、

【10日締めの翌10日後払い】

をご紹介していきます。

もし、翌5日後払いでも、
この後紹介していきます日程表の作り方をアレンジしてもらえれば対応可能です。

この手の日程表の作成は、ビジネス実践向けのマンツーマン講座で紹介をしておりますが、
様々な日付関係の関数が登場しますので、作れるようになるとExcel力もアップしますよ。

さて、毎回ご紹介しておりますが、
まずは祝祭日の一覧表を作っておきませんと避けることが出来ませんので、
確認しておきましょう。

そして、日程表のシートは、フレームを作成してあります。

A2には、2015と入力しており、2015年と表示するために、
前回同様にユーザー定義を使った表示形式で、2015年と表示しております。

B2には、A2と同様に、1と入力して、ユーザー定義を使って表示形式で1月と表示させています。
そして、左揃えの設定もしております。

確認の為、ユーザー定義の表示形式のダイアログボックスを表示しておきます。

日程表のA列も確認しておきます。
A5:A7までには1と10と20が入力されており、ここも日をつけて表示したいので、
表示形式のユーザー定義で表示を変えております。

A8には、末日と入力しております。末日は月によって28/29/30/31と変化しますので、
数値として入力することが出来ません。

では、B列の締日から作成してきますので、B5をクリックし、
DATE関数のダイアログボックスを表示しましょう。

ここには、DATE関数を使って算出していきますが、
末日のB8はEOMONTH関数を使っていきますので、順を追って説明していきます。

年には、A2をクリックして、絶対参照を設定しますので、$A$2とします。

オートフィルでB7までコピーをするために絶対参照を設定します。

月には、B2をクリックして、こちらも、絶対参照を設定しますので、$B$2。

そして、締日がそれぞれ異なりますので、
日には、A5を入力します。あとはOKボタンをクリックします。

オートフィルハンドルを使ってB7までコピーします。

面倒なのは、B8の末日。これは、EOMONTH関数を使わないといけませんので、
B8をクリックして、EOMONTH関数のダイアログボックスを表示しましょう。

先に、以前ご紹介しましたように月には0（ゼロ）を入力しておきます。

そして、開始日には、DATE関数をネストで入れていきますので、
開始日のボックスをクリックしてDATE関数のダイアログボックスを表示します。

年には、A2を入力。
月には、B2を入力。
日には、1を入力します。その月のどの日にちでもOKですので、
取りあえず1で入力しておくといいでしょう。

これで、OKボタンをクリックすると、末日も完成します。

ついでに、C列の曜日も以前紹介しておりますようにTEXT関数を使用して作成しておきます。
C5には、

=TEXT(B5,"aaa")

というTEXT関数を作成しておきましょう。

続いてD列の翌10日後払いを作っていきますが、ここが面倒なことになります。
オートフィルハンドルを使ってコピーという訳にはいかないからです。

1つずつ、数式の作り方が変わりますので、一つずつ確認していきましょう。

ですので、この【10日締めの翌10日後払い】は、総決算。スキル上達間違いなし。

それでは、

D5から確認しますので、DATE関数のダイアログボックスを表示します。

年には、A2をクリックして、絶対参照を設定しますので、$A$2。
月には、B2をクリックして、絶対参照を設定しますので、$B$2。
日には、10日になりますので、10と入力します。
OKボタンをクリックして、10日が完成しました。これを下のD6にコピーします。
続いて、D6をクリックして、20日を作っていきます。

これは、日のところだけ修正するだけですので、日を20と修正すれば完成します。
修正後OKボタンをクリックします。

これで、20日も完成しました。
続いていきましょう。D7には、20日の10日後なので、30日。

ならばいいのですが、だいたいケースとしては末日になることが多いので、
末日になるのでしたら、EOMONTH関数を使う必要が出てきますので、
EOMONTH関数のダイアログボックスを表示します。

開始日には、B7をクリックします。
月には当月の末日になりますので、0（ゼロ）と入力してOKボタンをクリックします。

そして、最後。末日が締日だったものは、翌月の10日になりますので、
DATE関数で数式を作成していきますので、DATE関数のダイアログボックスを表示しましょう。

年には、A2を入力します。
さきに、日には、10と入力します。
月には、締日の翌月になりますので、MONTH関数を使いますので、
MONTH関数のダイアログボックスを表示しましょう。

シリアル値には、B8をクリックして、OKボタンをクリックしましょう。
これで、D列の翌10日後支払も完成しましたので、E列の曜日も作成しておきましょう。
ここまで完成しましたね。

F列からI列までは、前回までと同様に作成すればいいので、ここからは、
簡単に説明していくことにします。

まず、WORKDAY関数を作っていきます。
WORKDAY関数は、土日を除いた日を指定された日数で算出、
しかも指定された日がある場合には、それを除いてくれるという関数なのです。

では、F5をクリックして、WORKDAY関数のダイアログボックスを表示しましょう。

開始日には、翌末払いのD5をクリックして、+1します。
日数には、-1と入力します。

祭日は、祝日一覧のシートに移動して、範囲選択をしますので、
祝日一覧!$A$2:$A$18と入力します。ここも絶対参照を設定しておきましょう。

オートフィルハンドルを使って12月まで算出して、ついでにお隣の曜日も算出しておきましょう。
これで、支払日が前日の場合が算出できました。

さぁ、最後の翌営業日のパターンを作って完成になります。あと一息ですね。
H5をクリックして、今回もWORKDAY関数を使いますので、
WORKDAY関数のダイアログボックスを表示しましょう。

開始日は、D5-1と入力します。
日数には、1と入力します。
祭日は、先ほどと同じ、祝日一覧!$A$2:$A$18と入力します。
絶対参照も忘れないようにしましょう。

では、OKボタンをクリックして、オートフィルハンドルを使って算出して、曜日も算出しておきます。これで完成しましたね。

このように、様々な支払パターンが存在しますので、試してアレンジしてみてはいかがでしょうか？

Excel。paymentday。20日末締めの翌10日払い。土日祝祭日に完全対応の日程表を作成してみる。

Excel。paymentday。20日末締めの翌10日払い。
土日祝祭日に完全対応の日程表を作成してみる。

WORKDAY関数とDATE関数

前回に引き続き、
土日祝祭日に完全対応した支払予定日の日程表を作成してみようというシリーズの今回は第2弾。

今回は、【20日締めの翌10日払い】をご紹介していきます。

締めが末締めばかりとは限りませんし、支払いだって、
様々ですから現場レベルに合わせて研修を行っているからこそ、
色んなパターンの日程表を作る練習をするわけですね。

さて、まずは祝祭日の一覧表が別シートに作成されていることを改めて確認しておきましょう。

これが無いと、祝祭日に対応できませんので作成しておきましょう。
支払いの日程表を作っていきますので、下記の表があります。

A5:A16までには、1～12までの月にあたる数値が入力しております。
そして、C2には、2015という数値が入力されていて、
ユーザー定義の表示形式で2015年と表示するように設定してあります。

セルの書式設定ダイアログボックスの表示形式を確認しておきましょう。

種類には、0"年"と入力してあります。
では、B列には、各月の20日を作っていきましょう。
B5をクリックして、日付を作っていきますので、DATE関数を使っていきます。
それでは、DATE関数のダイアログボックスを表示しましょう。

年は、2015が入力してある、C2。オートフィルハンドルで数式をコピーしていきますので、
絶対参照が必要になりますから、$C$2。

月には、A5。
日には、20日を設定したいので、20と入力しましょう。
そして、OKボタンをクリックします。あとは、12月まで数式をコピーしましょう。
ついでに以前の回でも紹介しておりますが、曜日も設定しておきます。

C5の数式は、
=TEXT(B5,"aaa")
TEXT関数を使用して曜日を表示させるようにしていましたよね。
TEXT関数のダイアログボックスも念のため確認しておきましょう。

まずは、ここまで完成しました。

続いて、翌10日払いを作っていきます。ここも日付を作る関数である、DATE関数を使います。
そして、翌月なので締日の翌月にする必要がありますので、
MONTH関数も合わせて使っていきます。

D5をクリックして、DATE関数のダイアログボックスを表示しましょう。

年には、先程と同じ$C$2。
月には、MONTH関数を使い翌月なので+1しますから、MONTH(B5)+1
日には、10日を作りたいので、10と入力しましょう。
そしてOKボタンをクリックして、これも12月まで数式をコピーします。
お隣のE列も先ほどのTEXT関数を使って曜日を算出しておきましょう。

さらに、ここまで完成しましたね。

そして、毎度のことながら問題になってくるのが、この翌10日が、
土日祝祭日になった時に、前日に払うのか？翌営業日でいいのか？ということになりますので、
ここから先のF列～I列までは、前回ご紹介したのと同じになります。

繰り返しになりますが、簡単に記載しておきます。
まず、WORKDAY関数を作っていきます。
WORKDAY関数は、土日を除いた日を指定された日数で算出、
しかも指定された日がある場合には、それを除いてくれるという関数なのです。

では、F5をクリックして、WORKDAY関数のダイアログボックスを表示しましょう。

開始日には、翌末払いのD5をクリックして、+1します。
日数には、-1と入力します。
祭日は、祝日一覧のシートに移動して、範囲選択をしますので、
祝日一覧!$A$2:$A$18と入力します。ここも絶対参照を設定しておきましょう。

オートフィルハンドルを使って12月まで算出して、ついでにお隣の曜日も算出しておきましょう。

これで、支払日が前日の場合が算出できました。

さぁ、最後の翌営業日のパターンを作って完成になります。あと一息ですね。
H5をクリックして、今回もWORKDAY関数を使いますので、
WORKDAY関数のダイアログボックスを表示しましょう。

開始日は、D5-1と入力します。
日数には、1と入力します。
祭日は、先ほどと同じ、祝日一覧!$A$2:$A$18と入力します。
絶対参照も忘れないようにしましょう。

では、OKボタンをクリックして、オートフィルハンドルを使って算出して、曜日も算出しておきます。これで完成しましたね。

次回は、10日毎締め翌10日後払いをご紹介します。

現場では短いサイクルで支払うことだってありますからね。いよいよ完結編です。