Excel 文字列の日付をシリアル値に変換(DATEVALUE関数)

2020年5月16日

DATEVALUE関数は文字列で表現されている日付を日付(シリアル値)に変換する関数です。

通常のセル入力では使用しませんが、文字列を結合して日付を作ったり

他アプリケーションから取り込んだ日付が文字列となっている場合があるので

それを日付に変換したい場合などに使用します。

時刻は切り捨てられてしまうので、それも含めて変換する場合はVALUE関数を使用しましょう。

仕様

=DATEVALUE(日付文字列)
引数省略時の値説明
日付文字列省略不可変換したい文字列を指定。
許容される範囲や形式はOSやバージョンに依存。
各引数の詳細

実行例

環境によって動作は異なりますが、

Office365のWIndows10の環境では、このように動作します。

DATEVALUE関数の実行例。正常ケース、形式混在ケース、形式不正ケースなど

結果として数値が表示されますが、書式設定で表示形式を日付に変更すると

日付文字列に対応する日付が表示されます。

日付の区切り(-と/)

日付の区切りは-でも/でも許容され、混在しても正常に変換されます。

年月日と和暦

年月日形式も元号も正常に変更されます。

OSとExcelが令和対応バージョンなら新元号も正常に扱われますが、

元年はエラーとなるのでSUBSTITUTE関数などで1年に変換する必要があります。

=SUSUBSTITUTE(日付文字列,"元年","1年")

また漢字の表記と-と/の区切りが混在してもエラーとなります。

英語表記

Jan(1月)のような表記もOKですが、

順番も英語表記として月日年とする必要があります。

スピルを利用する方法とメリット

Office365やExcel2019以降ではスピルという形式で記述可能です。

これは複数セルに数式をコピーする場合に便利な機能です。

スピルの利用には複数セルの範囲で指定します。

スピルでDATEVALUE関数を記述する例
スピルでDATEVALUE関数を記述する例

数式を入力するのは最初の一つのセルですが、

引数1で指定した高さ分、数式がセルのコピーなしで自動拡大されます。

B3に指定したDATEVALUE関数がB17まで自動拡大
B3に指定したDATEVALUE関数がB17まで自動拡大

スピルを利用することで今回の例では、

B3セルに入力するだけでよいため(B17以降にコピーペーストが不要)

以下のメリットがあります。

  • 入力の手間が少ない(数式のセルが多いほど効果が大きい)
  • 数式を編集した時、コピー忘れのリスクがない(最初のセルを更新すると全セルに反映)
  • 絶対参照が不要
  • 途中に行を追加した場合、数式のコピー&ペーストが不要。行削除でも壊れにくい。

関連記事

類似機能の関数

年、月、日を指定して日付(シリアル値)を取得(DATE関数)

年、月、日それぞれの数値を指定して日付を算出する同系の関数。

文字列の時刻をシリアル値に変換(TIMEVALUE関数)

関数の仕様と使い方の記事一覧