Excelのこと

DATE関数とWORKDAY関数の組み合わせで、支払日が自動的に入る支払通知書を作る

DATE関数とWORKDAY関数の組み合わせで、支払日が自動的に入る支払通知書を作るExcelのこと
この記事は約4分で読めます。

WORKDAY関数って便利

ExcelのWORKDAY関数は「ある開始日から●日後/前の日付を計算する」という関数で、
日付系関数の中でも使い勝手の良いものです。

例えば僕の場合、「稟議申請してから数日後に処理する」といったように
日付のルールがあるような場合、この申請書の「処理日」の項目に、

=WORKDAY(TODAY(),3,祝日リスト[日付])

といったように関数をいれています。(今日から3営業日後の日付を入力)

こうすると、いちいちカレンダーを見なくて済むようになるので、便利です。

ただ、個人的にWORKDAY関数は「●日後を算出する」という機能よりも、
「営業日かどうか判定する」という機能の方が、利用価値があるんじゃないかと思っています。

今回は、最近仕事で使うようになった
「DATE関数と組み合わせて、支払日が自動的に入る支払通知書を作る方法」を書いていきます

DATE関数との組み合わせで、支払日が自動的に入る支払通知書を作る

今回のケースでは、
「毎月はじめに、支払日が入った支払通知書を作成する」という業務を想定します。

支払通知書とはこういうものです。

支払通知書の例

この支払日の欄を埋めていくのですが、よくあるのが
「毎月15日。ただし、15日が休日の場合は、前営業日に支払うものとする」というケースです。

こうした場合、「決められた日(ここでは15日)が営業日かどうか判定し、休日なら前営業日にずらす」という処理を、DATE関数とWORKDAY関数の組み合わせで行います。

具体的にはこんな感じです。

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15)+1,-1,テーブル2[日付])

DATE関数の中身で「今月15日」を指定、+1で開始日を16日に指定
日数を-1して16日の1日前(つまり15日)が休日かどうかを判定します。

上記画像の場合、今月(2020年08月)は15日が土曜日なので、その前日である14日の日付が表示されています。

ちなみに、支払が翌営業日となる場合は、開始日を-1、日数を+1することになるので、

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15)-1,1,テーブル2[日付])

といった感じになります。

相手先によって支払日も変わる場合

相手先によって支払日も変わる場合もあります。

こうした場合は、相手先と支払日のリストを別シートに用意しておき、
その日付をルックアップして取得します。
関数のうち、DATEの”日”を指定する部分にVLOOKUPなどのルックアップ系関数を使います。

WORKDAY関数にDATE関数と、VLOOKUPを組み合わせ
相手先リスト

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),VLOOKUP(A3,相手先リスト,2,FALSE))+1,-1,祝日リスト[日付])

上記の場合、B興産の支払日”20日”を検索し、その日がもし休日なら前営業日になります。
C運輸なら、25日で同じことをします。

余談ですが、僕はこのように帳票作成の時にあらかじめ各項目に関数を入れておき、相手先の名前や特定のコードを使って項目が埋まるようにするのが好きです。

特定のコード(例えば「ABC001」)をもとに項目を埋めていく方法はマクロと相性が良く、FOR~NEXTなどのマクロを使えば末尾の数字を加算していくだけで、数十件の帳票が作れたりします。

まとめ

  • WORKDAY関数は「ある開始日から●日後/前の日付を計算する」という関数。
  • DATE関数と組み合わせると、ある特定の日付が営業日かどうか判断することもできる。
    (例:=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15)+1,-1,テーブル2[日付]) )
  • 相手先によって支払日が異なるような場合も、ルックアップ系関数との組み合わせで相手先に応じた日付を入れることができる。
    (例:=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),VLOOKUP(A3,相手先リスト,2,FALSE))+1,-1,祝日リスト[日付]) )

支払通知書の作成が数件であれば、カレンダーを見ながら作成することも可能ですが、
これが数十件、しかも相手先によって日付が異なるといった場合には
途端に時間のかかる業務になります。

日付に関するルールは決まりきったパターンが多いので、一度リストを作ったり、
関数を当てはめておくと、長くその効果を実感することができます。

タイトルとURLをコピーしました