Twitterを眺めていたら、日付データを8桁の数値データに置き換える場合、どうしたらいいんだろう? と考えました。

エクセルの講師とはいえ、自力では無理でしたね……。VALUE(ばりゅー)関数を業務で使ったこともないですし、MOS試験対策講座のエキスパートでも出てきませんから、かなりのレア関数です。まだまだ勉強が足りませんね。

【参考】エクセルで日付を数値データに

答えは、VALUE(ばりゅー)関数とTEXT(てきすと)関数を使って、日付データから数値データに変換することが可能ということ。上記回答にもあるように、早速試してみました。

また技がひとつ増えました(喜)

value

=VALUE(TEXT(日付の入ったセル,”yyyymmdd”))

業務に急いでいるときは、さっとこの公式を覚えてしまえばいいのですが、どういうカラクリか、講師らしく説明したいと思います。まず、VALUE(ばりゅー)関数から説明します。

VALUE関数とは

VALUE=日本語のエクセルで該当する言葉は「値」です。値のコピーとかありますよね。あれと同じ。なので、引数は1つしかなく、参照したセルの値を返します。

value3

上から順番に、日付ならシリアル値。文字列は使えません。時間は1日が1となるので、16時40分は0.69444……日という表示になります。これに24を掛けると時間が算出され、さらに60をかけると分になります。また、さらに60を掛ければ秒数が求められますね。パーセントスタイルも値のコピペをすれば、200%はただの「2」という値です。

文字列データ以外、値のコピー貼り付けで求められるデータです。これがVALUE関数です。

TEXT関数とは

TEXT関数は、参照したセルの表示形式を変更してくれる関数です。

=TEXT(セル番地,”セルの表示形式”)

引数は2つあり、第1引数には参照するセルを、第2引数には、表示形式を指定する記号を入れればOK。つまり、セルの書式設定ダイアログの中の、ユーザー定義に出てくるものを指定すればいいというわけです。

セルの書式設定はこちらです。赤枠の一覧を第2引数で指定すればいいのですね。

value4

セル「B2」に日付を入れた場合、第2引数をyyyyと入れたら2016だけの数値が出てきます。

value5

ゆえに、yyyymmddといれると、スラッシュなしの日付データ8桁が完成するわけですね。ここまで解説できると、なるほどと自己解決。この2つを組み合わせて使うと、

  1. TEXT関数で参照したセルの表示形式を定める。
  2. この表示形式変換により8桁表示となり、値のコピーが可能になる。
  3. VALUE関数で、出力されたデータが値に変換される。

という流れになり、2016/6/1 という日付データが、20160601というデータに書き換えられた、ということになります。カラクリを暴いてみると、なかなか面白いですね。

エクセルの先生であれば、この逆も知っておかないと、と思います。この逆は簡単でした。特に関数を使うことなく、「データ」タブより使うことができます。

まず、範囲選択をします。

value6

データタブより、「区切り位置」を設定します。

value7

区切り指定ウィザードの3番目、G/標準から日付を選択するだけでOK。データのインポートと同様の作業ですね。

value8

これで日付変換が完了です。

value9

【補足】

エクセルなら簡単ですが、Googleスプレッドシートだと難しいですよね。例えばセルB2に8桁の数値データが入っていたとしたら、

=left(B2,4)&”/”&mid(B2,5,2)&”/”&right(B2,2)

と、原始的な方法しか思いつきませんでした。まあ、スプレッドシートはいまのところ、ここまで煩雑な作業をすることはないので、OKということにしておきましょう。