いまさら聞けないExcelの使い方講座

【Excel】社員の定期券の有効期限を把握して事前に支給したい!エクセルで日付を計算するテク

社員の定期券の有効期限を管理したい

 社員の定期券の有効期限を把握して、期限が切れる前に交通費を支給しなければならなかったり、ソフトウェアやサービスなどの契約期限や保証期限を管理して、期限が切れる前に更新手続きをしなければならなかったりする時、どのようにしていますか。物理的に、カレンダーに印を付けるなどの管理方法ももちろん可能ですが、Excelの関数を使って管理することもできます。

 今回は、ExcelのEDATE関数とWORKDAY関数を使って、社員の定期代を管理する業務の例を考えてみましょう。

EDATE関数で○○か月後の日付を求める

 まずは、EDATE関数を使って、社員の定期券の有効期限を求めてみましょう。定期代管理表(①)のA列には社員の氏名、B列にはそれぞれの社員の定期券の利用開始日が入力されています。この表のC列に定期券の有効期限を求めてみます。ここでは、6か月定期を利用するものとします。

 EDATE関数は、指定した日付から起算して○○か月後(または○○か月前)の日付を求める関数です。「=EDATE(開始日,月数)」という書式で記述します。

 では実際に、セルC3に記述してみます。書式に当てはめて考えると、最初の引数には、利用開始日のセル「B3」(②)を、2つ目の引数には「6」(③)を入力すればよさそうですね。よって、「=EDATE(B3,6)」(④)と入力してみます。

 [Enter]キーを押して数式が確定されると、6か月後の日付(⑤)が表示されます。

 ここで考慮すべきことは、6か月定期の有効期限を考える場合、一般的に、6か月後の同じ日付の前日までが期限になるということです。よって、1日分引き算する必要があります(月末に購入する場合など、このとおりではないケースもありますが、例を簡素化するために1日分引き算するものとします)。

 数式を「=EDATE(B3,6)-1」(⑥)と修正して、[Enter]キーを押します。

 すると、6か月後の同じ日付の前日(⑦)が表示されました。

 オートフィル機能を使って、C列のほかのセルにも数式をコピーします。セルC3をクリックした状態でマウスポインターをセルの右下に合わせると、マウスポインターの形が変わる(⑧)ので、そのままドラッグ(⑨)します。

 C列すべてに数式がコピーされて、有効期限が求められました(⑩)。

WORKDAY関数で定期代の支給日を求める

 定期券の有効期限が求められたので、次は社員への定期代の支給日を求めてみましょう。ここでは、有効期限の5営業日前に定期代を支給するというルールがあるとします。

 まず、D列に「定期代支給日」を入力する列(①)を追加します。

 有効期限から5営業日前の日付を計算するには、WORKDAY関数を使います。WORKDAY関数は、指定した日付から○○日後(または○○日前)の営業日を求める関数で、「=WORKDAY(開始日,日数,祭日)」という書式で記述します。

 最初の引数には起算日、次の引数には日数を記述します。ここに正の数を記述すると、土日を除いて、その日数だけあとの日付が求められ、負の数を記述すると、土日を除いて、その日数だけ前の日付が求められます。最後の引数には、祝祭日や創立記念日など、土日以外で計算から外したい日付を指定します。

 では実際にやってみましょう。セルD3に「=WORKDAY(C3,-5,$H$2:$H$12)」(②)と入力します。最初の引数には有効期限のセル「C3」(③)を入力します。5営業日前の日付を求めたいので、次の引数には「-5」(④)と入力します。最後の引数には、事前に準備しておいた祭日のリストが入力されているセル範囲「$H$2:$H$12」(⑤)を入力します。数式を他のセルにコピーしても参照先が変わらないように、絶対参照で記述します。

 [Enter]キーを押して数式が確定されると、定期代の支給日(⑥)が表示されます。

 オートフィル機能を使って、D列のほかのセルにも数式をコピー(⑦)して、表を完成させます。

 これで、定期代の支給日を求めることができました。

日付を扱う関数は組み合わせると便利

 今回は、EDATE関数を使って社員の定期券の有効期限を調べたあと、WORKDAY関数を使って定期代の支給日を求める方法について解説しました。日付を扱う関数は、このように組み合わせて使うとより便利に使えます。

 ぜひ活用してくださいね。

今月のExcelTips