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日後払いをご紹介します。
現場では短いサイクルで支払うことだってありますからね。いよいよ完結編です。
