会社業務で利用しているシステムによっては、日付データを「yyyymmdd」形式の8桁の数字として保存していることがあります。
例えば「2006年3月25日」ならば、「20060325」という文字列でデータ格納しておくという方法です。
こういった形式で保存されているデータをExcelで利用する際に、テキストデータではなく日付データにしたいという要望をいただくことがあります。
TEXT関数と掛け算でyyyymmdd形式の数字を日付データに変換する
いくつかの変換方法がありますが、「日付データに変換するには何という関数を使えばいいのでしょうか？」
という形でご質問いただくことが多いので関数を使った方法をご紹介します。
※A1セルにyyyymmdd形式の8桁の文字列としての数字が入力されているときに、B1セルに日付データを作成する例
B1セルに
「=TEXT(A1, "0000!/00!/00") * 1」
という計算式を入力
↓
メニュー［書式］−［セル］をクリック
↓
［セルの書式設定］−［表示形式］タブをクリック
↓
［分類］欄で「日付」をクリックし、［種類］欄で適切な書式を選択
↓
［セルの書式設定］ダイアログ−［OK］ボタンをクリック
掛け算をする理由
B1セルの「=TEXT(A1, "0000!/00!/00") * 1」という数式の意味ですが、ポイントの一つは「* 1」という掛け算を行っている点でしょう。
掛け算を行わずに「=TEXT(A1, "0000!/00!/00")」とした場合、見た目には「2006/03/25」と日付データっぽく見えますが、TEXT関数は第1引数に指定された値を、第2引数の書式を設定した文字列に変換する関数なので、掛け算がない場合は単なる文字列になってしまいます。
Excelは日付を数値として扱っていますので、「* 1」という掛け算を行うことで数値データにしているわけです。
しかし、「* 1」をしただけでは「38801」といったシリアル値が表示されてしまいますから、［セルの書式設定］ダイアログで書式を設定を行っています。
TEXT関数の第2引数
TEXT関数の第2引数「"0000!/00!/00"」の、「!」が気になる方もいらっしゃるでしょう。
本当は「"0000/00/00"」と指定したいところですが、「!」なしで「/」が入ると割り算をする演算子として認識されてしまうため、年・月・日の区切りとして認識させるために「/」の前に「!」をつけています。
「=TEXT(A1, "0000-00-00")」
「=TEXT(A1, "0000""/""00""/""00")」
としてもOKです。
「=TEXT(A1, "0000""/""00""/""00")」
はわかりやすいとは思えませんが、日付の入力をするときに「3-25」のようにハイフンを使うのが慣れている方の場合は、
「=TEXT(A1, "0000-00-00")」
が一番簡単でしょう。
などもご紹介していますので、よろしければ合わせてご覧ください。
MID関数を使って日付データに変換する例
TEXT関数を使わずに、
「=(MID(A1,1,4) & "/" & MID(A1,5,2) & "/" & MID(A1,7,2)) * 1」
という数式でも同じ結果を得られます。
MID関数を使って、
「MID(A1,1,4)」（1文字目から4文字）
「MID(A1,5,2)」（5文字目から2文字）
「MID(A1,7,2)」（7文字目から2文字）
をそれぞれ取得して、間に「/」を入れて文字列結合しています。
Home » Excel(エクセル)の関数・数式の使い方 » 日付の処理・計算 » yyyymmdd形式の8桁の数字を日付データに−TEXT関数
