Excel関数 CSVファイルの数値を日付に変換し年齢を計算させる方法

   2015/03/16

002

ある社員の生年月日を別のソフトからCSV形式でダウンロードし、EXCELでダウンロードしたCSVファイルを開いて、社員の年齢を計算する方法のご質問をいただいたので、ここに記録しておきます。

年齢を計算するときは「DATEDIF関数」を使用します。

excel1
 

今回の場合、上記のようにセル「C2」に「DATEDIF関数」を入れればいいのですが、1つだけ問題があります。

「古い日付」が存在していません。
存在しているのは「古い日付」の手がかりとなるものだけです。

その手がかりがこちらです。

excel2
 

この手がかりから言うと、この社員の生年月日は「平成2年1月5日」です。

セルの中に表示されているのは単なる数字の羅列だけで、これを日付として計算させるためには、この手がかりを「シリアル値」に置き換えなくては行けません。

※シリアル値とは、日付を計算するために裏で動いている数値です。

1900年1月1日をシリアル値「1」とし、それ以降に「1、2、3・・・・」とシリアル値が存在しています。

ちなみに「平成2年1月5日」のシリアル値は「32878」です。

1900年の1月1日から数えて「32878」日目という意味だと思ってください。

 

「古い日付」をシリアル値に置き換えて表示させましょう!

まず、2つのセル「A2:B2」に入力されているデータを元に下のように書き換えてみましょう。

excel3
 

そのためには、もう一度手がかりを良く確認しましょう

excel2
 

セル「A2」のデータを元に「S」か「H」を抽出しましょう


「IF関数」を使用して、「A2」のデータが「3」だったら「S」、「3以外」だったら「H」とします(セル「A2」には3か4しか入力されていないと仮定します)。

excel4

=IF(A2=3,”S”,”H”)

 

セル「B2」のデータを元に年数を抽出しましょう


次にセル「B2」のデータから年数を抽出する必要があります。

年数も確実に2桁表示でしたら「LEFT関数」を使用して左の2桁の数値を返せば(表示させれば)いいのですが、今回は、1桁の場合と2桁の場合が存在します。

なので、「INT関数」を使用して10000の位を整数で表示させましょう。

つまり、1000の位以下を切り捨てます。

excel5

=INT(B2/10000)&”.”

 

セル「B2」のデータを元に月数を抽出しましょう


次にセル「B2」のデータから月数を抽出します。

今回も「INT関数」を使用しますが、右側4桁の数字を100のくらいで整数表示させたいですので、「RIGHT関数」も使用します。

excel6

=INT(RIGHT(B2,4)/100)&”.”

 

セル「B2」のデータを元に日数を抽出しましょう


次にセル「B2」のデータから日数を抽出します。

今回は右の2桁を抽出させるということで「RIGHT関数」を使用します。

excel7

=RIGHT(B2,2)

 

そしての関数を合体させます。

同じセルの中に別の者同士を一緒に表示させる時に使用するのが「&(アンパサンド)」です。

イメージは「①&②&③」です。

※年数と月数の後ろにある「&”.”」も&を使用して関数と「.(ピリオド)」をくっつけたものです。

=IF(A2=3,”S”,”H”)&INT(B2/10000)&”.”&INT(RIGHT(B2,4)/100)&”.”&RIGHT(B2,2)

 

ただし、このままだとシリアル値として判断しませんので、正しい計算ができません。

上記の結果をシリアル値に置き換えるために「DATEVALUE関数」を使用します。

使い方は簡単です。

頭にDATEVALUEを入力し先ほどの関数をまとめて( )でくくるだけです。

=DATEVALUE(IF(A2=3,”S”,”H”)&INT(B2/10000)&”.”&INT(RIGHT(B2,4)/100)&”.”&RIGHT(B2,2))

 

出来上がった関数を「DATEDIF関数」の「古い日付」の位置にはめ込みましょう!

=DATEDIF(DATEVALUE(IF(A2=3,”S”,”H”)&INT(B2/10000)&”.”&INT(RIGHT(B2,4)/100)&”.”&RIGHT(B2,2)),TODAY(),”Y”)

出来上がりました!

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

コメントを残す