記事の詳細
初心者でも簡単!EXCEL DATEDIFを使って、在職と退職者を区別した計算方法とそのエラー対策!
こんにちは。
火曜日ブログ担当の田中です。
前回のブログ
「ExcelのDATEDIFを使って日付の計算をして経過日数(勤続年数)を求める方法!」
の続きになります。
今回は
「在職と退職を区別した時の計算」と「DATEDIF関数で陥るエラー対策」
です。
(過去記事は下記画像リンクからどうぞ)
今回のブログの見出しはコチラです!
在職と退職を区別した時の計算
前回の続きですが、前回DATEDIFという関数を使って、勤続年数を出しました。
前回の計算式ですと、退職者も管理していた場合
退職しているにも関わらず、勤続年数がカウントされていしまいます。
下の表の社員Aと社員Cは同じ日の1984年4月1日に入社し、Cは2000年の3月31日に退職をしています。
ところが勤続年数が同じになってしまっています。
これを退職した場合は“退職日”で計算をするようにします。
前回の式
=DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”
の式に
退職日にデータがあったら、退職日を見て、なければ今日の日付で計算をする
という式を追記します。
追記した式が下になります。
退職をした日で計算をする式はこれ!
=IF(【退職日(終了日)】=””,DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”,DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”Y”)&”年”&DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”YM”)&”ヶ月”)
※上の式をコピーして【入社日(開始日)】、【退職日(終了日)】はご自身のエクセルと合わせて使用して下さい。
こちらTEST社員名簿で言いますと入社日はC5、退職日はE5と【入社日(開始日)】、【退職日(終了日)】をそれぞれ書き換えます。
計算すると下のようになります。
社員Aは今日までの日で、社員Cは退職日で計算されています。
式の解説とエラー対策
=IF(【退職日(終了日)】=””,DATEDIF(【入社日(開始日)】,TODAY(),”Y”)&”年”&DATEDIF(【入社日(開始日)】,TODAY(),”YM”)&”ヶ月”,DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”Y”)&”年”&DATEDIF(【入社日(開始日)】,【退職日(終了日)】+1,”YM”)&”ヶ月”)の式の解説をします。
大きく分けますと3つに分かれます。
①IF文
=IF(E5=””)でE5のセル(退職日)が空欄だったら、②の式を、なんらかの値が入っていたら③の式を実行しています。
社員Aは退職していないので空欄になっています。
なので、②の式を実行します。
社員Cは退職しているので退職した日付が入っています。
なので、③の式を実行します。
②入社日から今日の日付で計算
これは前回のブログの記事と同じ計算です。
開始日=入社日
終了日=Today関数
を使う事で、今日までの日付で勤続年数を出す事ができます。
③入社日から退社日で計算
③の式では
開始日 = 入社日
終了日 = 退職日+1
となっています。
これは、これは退社日が3月31日だった場合、DATEDIFでは前日の30日までの計算になってしまいます。
31日を含んでほしいところですが…
通常31日付の退職の場合、31日は働いたという扱いになるはずです。
このままですと31日は働いていない状態になってしまうので、+1して調整をします。
これは実際に見た方が早いと思いますので、比較してみます。
退職日に+1しない計算
退職日に+1した計算
というように+1としてあげる事で正しく調整する事ができます。
このDATEDIF関数は勤続年数に限らず他にも活用できます
是非活用してみてください。
Thank you for reading my blog.
See you next time.
Have a nice day.
【PR】相模原市産業会館パソコン教室
相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。
コメント
この記事へのトラックバックはありません。
この記事へのコメントはありません。