記事の詳細

こんにちは。
火曜日担当の田中です。

気づいたら寒い季節になりましたね。
野菜も価格が高騰していて中々元に戻りません。
野菜を買わずに我慢していたら口内炎が…
やっぱり、高くても野菜は食べなくてはいけませんね。

塩分がしみる…(´;ω;`)

気分を切り替えて、今日のExcelのテーマは日付です。

「Excelで社員名簿を作っている」もしくは、「これから作ろうとしている」

方にとって参考になると思います。
式もコピーできるようにしていますので、是非読んでみて下さい(^^♪

 

DATEDIFを使って”社歴”(勤続年数)の計算をする

会社によると思いますが、勤続年数による表彰だったり、勤続年数による退職金など、勤続年数を調べる機会があると思います。
アナログな計算もいいですが、Excelでは計算する機能が備わっています。
それを使って計算の仕方を書きます。

使う関数は

DATEDIF()

という関数を使います。

この関数は、日付を2 つの日付の間の日数、月数、年数を計算してくれます。

なんとも便利ですね(^^♪

使い方ですが、

「開始日,終了日,単位」

を入力する事で使う事ができます。

実際にこの関数を下の社員名簿で使ってみます。

employee_list_01

開始日、終了日、単位を指定する。

employee_list_02

①で開始日を設定しています。
社歴を求めるので、開始日は入社日になります。
なので、入社日を入れます。

②にて終了日を設定するのですが、まだ退職していない場合終了日を設定できませんよね。
ここでは、今日までの日付を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のシートに合わせて修正して使って下さい。

これから式の説明をしていきます。

 

employee_list_03

①でやっている事は、開始日から終了日の間の年数のみを取得しております。

②、④ですが、DATEDIFで出力されるのは数値だけです。
なので、”年”と”ヶ月”を文字列で補っています。

③では、①では年しか求める事ができないので、「何か月なのか」を別に求める必要があります。
①の関数と同様にDATEDIFで算出し、単位だけを”YM”にします。

月を求める”M”を使わない理由は、単位のところで説明をしておりますが、Mを使うと、期間内の総月数が出力されてしまう為、1年の末日でリセットのかかる“YM”の方を使っています。

あとは他の社員にも適用させてあげるので、マウスを右下の+に合わせてドラッグします。

employee_list_07

employee_list_05

という感じになりました。

次回は、「DATEDIF関数で陥るエラー対策」と「在職と退職を区別した時の計算」

を記載したいと思います。

 

Thank you for reading my blog.
See you next time.
Have a nice day.

この記事であなたの課題を解決することができましたか?

疑問点があったり、解決できなかったことがありましたら、お気軽にご相談してください。

The following two tabs change content below.
田中 優樹

田中 優樹

価値あるコンテンツ制作部インクループ株式会社
助成金、社内事務、イベント運営と、製作部隊ではなく裏方作業担当の田中です(^^♪ 助成金は「自分達で調べ申請する」シリーズで作成しています。 やりたい事色々。相模原を盛り上げたい!世界と日本をつなぎたい!!以上!!

ほかの投稿も読んでみませんか?

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

お知らせ

平成28年度補正予算 小規模事業者持続化補助金

特集

エクセルトラブルと解決策のまとめ アクセス数を増加させるための方法まとめ 初心者向けWordPressの設定方法まとめ

AD

ページ上部へ戻る