記事の詳細
ExcelのDATEDIFを使って日付の計算をして経過日数(勤続年数)を求める方法!
こんにちは。
火曜日担当の田中です。
気づいたら寒い季節になりましたね。
野菜も価格が高騰していて中々元に戻りません。
野菜を買わずに我慢していたら口内炎が…
やっぱり、高くても野菜は食べなくてはいけませんね。
塩分がしみる…(´;ω;`)
気分を切り替えて、今日のExcelのテーマは日付です。
「Excelで社員名簿を作っている」もしくは、「これから作ろうとしている」
方にとって参考になると思います。
式もコピーできるようにしていますので、是非読んでみて下さい(^^♪
今回のブログの見出しはコチラです!
DATEDIFを使って”社歴”(勤続年数)の計算をする
会社によると思いますが、勤続年数による表彰だったり、勤続年数による退職金など、勤続年数を調べる機会があると思います。
アナログな計算もいいですが、Excelでは計算する機能が備わっています。
それを使って計算の仕方を書きます。
使う関数は
DATEDIF()
という関数を使います。
この関数は、日付を2 つの日付の間の日数、月数、年数を計算してくれます。
なんとも便利ですね(^^♪
使い方ですが、
「開始日,終了日,単位」
を入力する事で使う事ができます。
実際にこの関数を下の社員名簿で使ってみます。
開始日、終了日、単位を指定する。
①で開始日を設定しています。
社歴を求めるので、開始日は入社日になります。
なので、入社日を入れます。
②にて終了日を設定するのですが、まだ退職していない場合終了日を設定できませんよね。
ここでは、今日までの日付をToday()を使って算出します。
※TODAYの日付ですが、2016年11月15日です。
③単位の設定ですが、「Y」を設定しましたが、これは「年」を表します。
何年働いているのかを出力しています。
これにさらに〇〇年と△△ヶ月という期間も追記したいですよね。
DATEDIFの単位を変えるだけで更に詳しい期間を取得する事ができます。
単位の種類ですが、6種類あります。
次のセクションで説明いたします。
DATEDIF関数内の単位の一覧
単位は「Y、M、D、YM、MD、YD」の6種類です。
単位「Y」を設定した場合:
求める期間の年だけを出力します。
2014年1月1日~2016年1月1日であれば、2という値が出力されます。
単位「M」を設定した場合:
求める期間の月だけを出力します。
2014年1月1日~2016年1月1日であれば、24という値が出力されます。
期間の月の総数が出力されます。
単位「D」を設定した場合:
求める期間の日だけを出力します。
2014年1月1日~2016年1月1日であれば、730という値が出力されます。
(365日×2年間です。)
期間の日の総数が出力されます。
単位「YM」を設定した場合:
これは前に記述しました、「M」と同様に月を出力します。
ただし、こちらの単位では、2014年1月1日~2016年1月1日を調べると、0という値が出力されます。
これは月と月の比較になり、12ヶ月目の次はリセットされ、また0から始まります。
2014年1月1日~2016年1月1日であれば2年0ヶ月というイメージです。
単位「MD」を設定した場合:
これは前に記述しました、「D」と同様に日を出力します。
こちらも前の「YM」と同様に2014年1月1日~2016年1月1日を調べると、0という値が出力されます。
こちらの単位では日付と日付の比較になります。
なので、1日同士の比較なので0になります。
2014年1月1日~2016年1月1日であれば2年0ヶ月0日というイメージです。
単位「YD」を設定した場合:
これは前に記述しました、「MD」と同様に日を出力します。
MDとの違いは、1月1日と2月1日を比較した時に、「MD」では0になり、YDでは「31」になります。
MDでは月末日を迎えると「0」になります。YDでは1年の末日を迎えると「0」(365もしくは366日を迎える時)になります。
社歴(勤続年数)○○年△△ヶ月まで出力する方法
ここから、○○年△△ヶ月の出力の方法を記載します。
数式をコピーしたい方はこの下の式をコピーして使用してください。
=DATEDIF(【開始日】,TODAY(),”Y”)&”年”&DATEDIF(【開始日】,TODAY(),”YM”)&”ヶ月”
※【開始日】のところをお客様のExcelのシートに合わせて修正して使って下さい。
これから式の説明をしていきます。
①でやっている事は、開始日から終了日の間の年数のみを取得しております。
②、④ですが、DATEDIFで出力されるのは数値だけです。
なので、”年”と”ヶ月”を文字列で補っています。
③では、①では年しか求める事ができないので、「何か月なのか」を別に求める必要があります。
①の関数と同様にDATEDIFで算出し、単位だけを”YM”にします。
月を求める”M”を使わない理由は、単位のところで説明をしておりますが、Mを使うと、期間内の総月数が出力されてしまう為、1年の末日でリセットのかかる“YM”の方を使っています。
あとは他の社員にも適用させてあげるので、マウスを右下の+に合わせてドラッグします。
という感じになりました。
次回は、「DATEDIF関数で陥るエラー対策」と「在職と退職を区別した時の計算」
を記載したいと思います。
次回の記事はコチラです。
Datedifはわかったけれど、休日を除いた計算ってできるの?
Datedifは指定した期間の日数を求める事ができますが、休日を除いた計算をしたい時ありますよね。
そんな時はこの記事を読むと解決できると思いますよ。
※2018年2月15日追記
Thank you for reading my blog.
See you next time.
Have a nice day.
【PR】相模原市産業会館パソコン教室
相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。
コメント
この記事へのトラックバックはありません。
この記事へのコメントはありません。