Excel
Excel関数

Excelの日付に関する書式とTEXT関数

今回のケースについて

あるシート上で関数を入力しても正式に動作しないという事象を確認したとき、書式が原因であるというケースがありました。
そこで今回は日付の値である「#2018/5/14#」、式としては「=TODAY()」を様々な書式で見た場合どうなるかという点について調べてみました。

標準(yyyy/m/d)

2018/5/14

あくまで平常通り。
月と日の部分は2文字にすると2桁表記になります。

文字列(@)

=TODAY()

シートの計算が有効になっていても計算が正常に行われない場合、書式が文字列になっている可能性があります。
大体の理由としてはExcelは自動的にゼロ抑制を行います。
そこで、0から始まるIDなどの入力を要求する際に不都合が生じるため明示的に文字列として入力を行うシングルクォートを入力せず入力させたい場合はこの書式を用いています。

数値(表示形式は<0_);[赤](0)>)

43324(※1は1900/1/1)

なんの値だよ!ということで上に注釈をつけています。

何が問題か

問題はExcelの関数で発生します。
&演算子による文字列結合やCONCAT、CONCATENATE関数を使用した時において数値をベースとして変換します。
そのため、この場合だと43324として出力されます。

解決法

Excelシート上ではTEXT関数を使用します。(VBAについてはFormatメソッドで解決可能です。)
TEXT関数は第2引数で入力された書式にそって第1引数に入力された値を文字列型に変換して返します。

たとえば、TEXT(DATE(2018,5,14),"yyyy-m-d")の戻り値は"2018-5-14"として帰ってきます。
DATE関数は年月日をそれぞれ入力した時、日付型の値として帰ってきます。

最後に

よくわかんないけど日付が変な値になったとか、式がそのまま出てくるとかいう場合は書式を疑うのが先決だと思いました。