記事の詳細

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

前回のブログ

「ExcelのDATEDIFを使って日付の計算をして経過日数(勤続年数)を求める方法!」

の続きになります。

今回は

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

です。

(過去記事は下記画像リンクからどうぞ)

こんにちは。火曜日担当の田中です。気づいたら寒い季節になりましたね。野菜も価格が高騰していて中々元に戻りません。野菜を買わずに我慢していたら口内炎が…やっぱり...

在職と退職を区別した時の計算

前回の続きですが、前回DATEDIFという関数を使って、勤続年数を出しました。

前回の計算式ですと、退職者も管理していた場合

退職しているにも関わらず、勤続年数がカウントされていしまいます。

下の表の社員Aと社員Cは同じ日の1984年4月1日に入社し、Cは2000年の3月31日に退職をしています。
ところが勤続年数が同じになってしまっています。
excel_datedif_20161122_003

これを退職した場合は“退職日”で計算をするようにします。

前回の式

=DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”

の式に

退職日にデータがあったら、退職日を見て、なければ今日の日付で計算をする

という式を追記します。
追記した式が下になります。

 

退職をした日で計算をする式はこれ!

=IF(【退職日(終了日)】=””,DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”,DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”Y”)&”年”&DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”YM”)&”ヶ月”)

※上の式をコピーして【入社日(開始日)】、【退職日(終了日)】はご自身のエクセルと合わせて使用して下さい。
こちらTEST社員名簿で言いますと入社日はC5、退職日はE5と【入社日(開始日)】、【退職日(終了日)】をそれぞれ書き換えます。

計算すると下のようになります。

社員Aは今日までの日で、社員Cは退職日で計算されています。

excel_datedif_20161122_004

 

式の解説とエラー対策

=IF(【退職日(終了日)】=””,DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”,DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”Y”)&”年”&DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”YM”)&”ヶ月”)の式の解説をします。

大きく分けますと3つに分かれます。

excel_datedif_20161122_005

①IF文

=IF(E5=””)でE5のセル(退職日)が空欄だったら、②の式を、なんらかの値が入っていたら③の式を実行しています。
社員Aは退職していないので空欄になっています。
なので、②の式を実行します。

社員Cは退職しているので退職した日付が入っています。
なので、③の式を実行します。

②入社日から今日の日付で計算

これは前回のブログの記事と同じ計算です。

開始日=入社日
終了日=Today関数
を使う事で、今日までの日付で勤続年数を出す事ができます。

③入社日から退社日で計算

③の式では
開始日 = 入社日
終了日 = 退職日+1
となっています。

これは、これは退社日が3月31日だった場合、DATEDIFでは前日の30日までの計算になってしまいます。
31日を含んでほしいところですが…

通常31日付の退職の場合、31日は働いたという扱いになるはずです。
このままですと31日は働いていない状態になってしまうので、+1して調整をします。

これは実際に見た方が早いと思いますので、比較してみます。

退職日に+1しない計算

excel_datedif_20161122_007

 

退職日に+1した計算

excel_datedif_20161122_006

というように+1としてあげる事で正しく調整する事ができます。

このDATEDIF関数は勤続年数に限らず他にも活用できます
是非活用してみてください。

 

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

【PR】相模原市産業会館パソコン教室

相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。

bana-pasoken-blog

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

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


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

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

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

相模原のお店やサービスをどんどん紹介!

行ってみよう相模原 相模原市のお店限定

飲食店

ページ上部へ戻る