Excel使いこなしの基礎と応用

 

土日と祝日を除いた期日を求めるWORKDAY関数

請求書発行日から10営業日以内の支払期限を設けた場合などで、土・日・祝祭日を除外して期日を導き出す時はWORKDAY関数を使います。祭日を除外するために祝日リストが必要になる例があるので、その作成と管理についても解説します。

目次


WORKDAYで土日祝日を除いた10営業日後の支払期限を求める

請求書の発行日「F1」セルを開始日として、土日と祝祭日を除いた10営業日後の日付を支払期限「C6」セルに表示させてみます。
発行日を開始日として支払期限を求める

祝日を除外するには別シートに日付の一覧表を用意する必要があります。祝日リストの作り方については後述するとして、下図のように日付を羅列した範囲に「祝祭日」などと名前を定義しておきます。
祝日リストの範囲に名前を定義
支払期限を入力するセルを選択し、「数式」タブ「関数ライブラリ」の「日付/時刻」▼から「WORKDAY」をクリックします。
関数ライブラリの日付/時刻▼からWORKDAYをクリック
WORKDAY関数の引数は3つ。
WORKDAY関数の引数
開始日」には請求書の発行日を入れるセル番地を指定します。
日数」には支払い期限までの日にち「10」を入力。これは「10日後」と同義になります。※「10日前」の日付を求めたい時は「-10」
祭日」には別シートに用意した祝日表に定義した名前を入力。※この引数を省略すると土日のみが除外

数式はこうです→「=WORKDAY(F1,10,祝祭日)

請求書発行日を仮に「2018/1/4」とすると、土日祝日を除いた10日後は「2018/1/19」になるわけですね。
10営業日後の期日
セルに発行日を入力して確認。
発行日を入力して支払期限の表示を確認
シリアル値が表示されてしまったら、セルの表示形式を「日付(上図の表記は「長い日付」)にしておきましょう。
シリアル値が出たら表示形式を日付に変更

開始日(請求書発行日)の未入力時に1900年の日付が返される?

スポンサーリンク

開始日の日付が未入力の状態だと普通エラーが出そうなものですが、この場合は「1900年1月13日」という日本だと明治時代の日付が出てきちゃいます。
開始日が未入力の時に1900年の日付が出る
シリアル値は時の経過を数値化したものですが、この起点となる日が「1900年1月1日」で、この日をシリアル値「1」として計算されます。日付の欄の空白はこの起点日と見做され、土日を除外した10日後の期日が返されたというわけです。

これを消したい場合はIF関数に入れ子します。
=IF(F1=””,””,WORKDAY(F1,10,祝祭日))
「もし、請求書発行日が空欄なら、支払期限も空欄にします空欄でなければ(日付が入力されれば)その日を開始日として土日祝日を除いた10日後の期日を表示します

祝日リストの作成と管理

WORKDAYを使ったこの例題の一番のネックは祝日のリストを別シートに作って管理しなければならない点です。短期間を対象にするなら簡単な話ですが、数年にまたがって機能させたいなら毎年データを更新する必要があります。国民の祝日は増えることもあるし、振替休日のあるなしも毎年変わるので、年度初めには最新の情報内閣府から配布資料がダウンロードできます)を入手するようにします。

スポンサーリンク

当サイトのExcelで万年カレンダー仕様の予定表を作るで無料ダウンロードできるファイルに祝日のデータが添付されています。これを利用してリスト表を作る手順を簡単に書いておきます。

ダウンロードしたスケジュール表の「祝日表」というシートを開き、祝日表の範囲を選択してコピー。
祝日リストの範囲をコピー
請求書のファイルにシートを追加して、ペーストします。A列の「」を当該年度の数字に置換え(例えば2016→2018)ると、祝日表のデータが置換えた年度のものに入れ替わります。
祝日表の年度を切り替える
空の行が幾つかあるので、それをControlキーを押しながら選択し、「ホーム」タブの「セル」グループ「削除▼」から「セルの削除」をクリック、「上方向にシフト」で「OK」。※これらの空の行は年度をスイッチする度に出現します。仕様上どうしてもこうなるので、年度入れ替えの度に削除して下さい。
空の行を削除して上へシフト
日付」の列範囲を選択して上述したように名前を定義し、表示形式を「長い日付形式」に設定し直しておきます。
祝日リストの日付の範囲の表示形式を変えて名前を定義

祭日が増えたり、正月三賀日や事業所の夏休みなどを入れるなら、行を追加して対処して下さい。

▶ 土日以外の定休日と祝日を除いた~日後の期日を求めるならWORKDAY.INTL

Officeで作れるもの