実務で使えるExcel入門セミナー 「年齢」「勤続年数」など期間を算定するなら「DATEDIF」関数で! 使い方と注意点を分かりやすく解説

シェアする

  • このエントリーをはてなブックマークに追加

人事や経理で仕事をしていると、従業員の年齢や勤続年数など期間を計算する場面がよく出てきます。そんな時は「DATEDIF関数」を使うと便利です。勤続年数の算定と賞与の支給倍率の表示を例に「DATEDIF関数」の使い方を説明して行きます。

”さらっ”とまとめると
・DATEDIF関数は、「開始日」と「終了日」を入力することで期間を算定する関数

・表示方法を指定することで、「年」「月」「日」と期間の表示のしかたを変えることができる
・実務で「DATEDIF関数」を使う時は、ただ期間を表示するだけでなく「IF関数」や「条件付き書式」などと組み合わせて使うことを考える

スポンサーリンク
google adsense




DATEDIF関数の基本的な使い方と入力方法

DATEDIF関数は、開始日終了日を入力することで期間を算定する関数です。入力する項目も①「開始日」②「終了日」③「表示方法」の3つしかないので、シンプルで使いやすい関数と言えます。

スクリーンショット 2017 07 08 11 38 37

ただし、注意が必要な点もあります。

「開始日」「終了日」の入力方法

「開始日」「終了日」の入力方法ですが、日付をそのまま入力してもエラーになってしまうので注意が必要です。

開始日を2017年7月7日、終了日を2020年8月31日とする場合。Excelで日付を入力する際は、「2017/7/7」のように、スラッシュ(/)で「年・月・日」を区切りますが、この方法で入力すると、

スクリーンショット 2017 07 08 11 56 31

エラーになってしまいます。

DATEDIF関数を正しく機能させるためには、日付を入力したセルを指定する方法(=セルを参照する方法)か、DATE関数を使う方法で入力します。

スクリーンショット 2017 07 08 12 21 08

スクリーンショット 2017 07 08 12 20 00

実務上は、何の期間を計算しているのかがはっきり分かるので、開始日、終了日が入力されているセルを指定する方法(セルを参照する方法)で入力するのがおすすめです。

表示方法の指定

DATEDIF関数は、表示方法を指定することができます。

」で表示するなら「Y
」で表示するなら「M
」で表示するなら「D

と指定しますが、その際「Y」「M」「D」を「” ”」(ダブルクォーテーション)で囲む必要があります。

もし、「Y」のように「” ”」で囲むのを忘れてしまうと、

スクリーンショット 2017 07 08 12 50 02

エラーになってしまいます。
表示方法を指定する時は必ず、「”Y”」「”M”」「”D”」と「” ”」で囲むようにしましょう。

スクリーンショット 2017 07 08 12 57 40

DATEIF関数で算定される期間は、端数が切り捨てられている

DATEDIF関数で算定された期間にも注意が必要です。
DATEIF関数で表示される期間は、端数が切り捨てられています。

たとえば、2017年1月1日から2020年3月31日までの期間を、DATEIF関数で算定して「年数」で表示させてみます。

スクリーンショット 2017 07 08 13 37 58

2017年1月1日から2020年3月31日までの期間は、3年3ヶ月ですが表示方法で「年」を指定すると、「月」以下の数字は切り捨てられているのです。なので、DATEIF関数で表示されている期間には、端数があることに注意する必要があります。

なお、指定した表示方法では表示されない端数を表示する方法もありますが、そちらは後で説明します。

DATEDIF関数で勤続年数に応じた賞与支給倍率を表示させる

DATEDIF関数の実務での使い方の1つとして、勤続年数に応じた賞与支給倍率を表示させてみます。まず、Excelで従業員の一覧を作成します。

スクリーンショット 2017 07 08 13 01 00

勤続年数を計算するために、各従業員の入社年月日が入力されているのがポイントです。ここは、従業員のデータを元に手動で入力する必要があります。

支給倍率は、勤続年数によって次のように決められているとします。
実務では、会社が作成する支給倍率の表を利用します。

スクリーンショット 2017 07 08 13 06 56

賞与の計算では基準日が決められていますので、勤続年数は入社日から基準日までの期間で計算します。ここで使うのがDATEDIF関数です。支給倍率は「勤続年数」にって決められているので表示方法は「”Y”」を指定します。

実際に入力してみましょう。

スクリーンショット 2017 07 08 14 43 50

表示方法の「”Y”」さえ間違えなければ、特に問題なく入力できると思います。入力結果を確認しておくと、入社日1990年9月1日、基準日2017年6月1日で、勤続年数「26」年と正しく表示されています。

あとは、IF関数を使って勤続年数に応じた支給倍率を表示させるだけです。IF関数の入力は、条件が複雑になる場合は、一度にまとめて入力してしまうのではなく、条件ごとに入力内容を整理した上で、最後にまとめて入力するようにすればうまく行きます。

※「F4」は勤続年数が入力されているセル

・30年以上=2.5 IF(F4>=30, 2.5

・20年以上=2.2 IF(F4>=20, 2.2

・10年以上=2.0 IF(F4>=10, 2

・上記以外=1.8 1.8

以上をまとめると、

スクリーンショット 2017 07 08 14 35 48
スクリーンショット 2017 07 08 14 46 32

他のセルにもIF関数をコピー&ペーストすれば、

スクリーンショット 2017 07 08 14 50 51

DATEDIF関数の端数を表示させる方法

先ほど説明した通り、DATEDIF関数で表示される期間は端数が切り捨てられています。たとえば、DATEDIF関数で算定した期間が「3年2ヶ月10日」だった場合、表示方法を「年」に指定すると、2ヶ月10日は切り捨てられて「3」と表示され、「月」に指定すると、10日は切り捨てられて「38」と表示されることになります。

ですが、表示方法の指定の仕方を変えると、切り捨てられる端数の期間を表示させることもできます

具体的には、表示方法を次のように指定します。

・年未満の月数を表示:  ”YM”

・1ヶ月未満の日数を表示: ”MD”

実際にやってみます。開始日を2017年1月1日、終了日を2020年3月11日としてDATEDIF関数で期間を求めます。この期間は、3年2ヶ月11日になりますが、表示方法を”YM”に指定すると「年」に満たない月数が表示され、

スクリーンショット 2017 07 08 15 05 59

表示方法を”MD”に指定すると、「月」に満たない日数が表示されます。

スクリーンショット 2017 07 08 15 06 06

期間の算定結果を「○年△月×日」のような形で表示させたい場合は、このような表示方法の指定の仕方をしっておくと便利です。

まとめ

実務で期間の算定が必要な時は、DATEDIF関数を使うのが便利です。表示方法の指定の仕方が少し難しいですが、そこさえクリアできれば関数自体はシンプルですので幅広い用途で使うことができます。

実務で使えるExcel入門セミナー
実務で使えるExcel入門セミナー

 

経理、税務、財務の実務に特化した『実務で使えるExcel入門セミナー』を開催します。
「近日出荷」を運営する高橋良和が公認会計士・税理士として監査、経理、税務の現場で実際に使ってきたExcelスキルを直接お伝えする講座です。

就職・転職で「Excelなら使えます」とアピールしたい
Excelで同僚に差をつけられたくない
実務レベルで使うExcelのテクニックが知りたい

など教科書では学べない実務の現場で"使える"実践的スキルをお伝えします。

 
 

【実務で使えるExcel入門セミナー】
基礎編:10時10分〜 詳細はこちら
応用編:14時〜 詳細はこちら
場所:東京都千代田区セミナールーム
日程:10/7(土),14(土),21(土),28(土), 11/4(土),18(土),25(土)

 

 

スポンサーリンク
google adsense




google adsense




シェアする

  • このエントリーをはてなブックマークに追加

フォローする

error: Content is protected !!