Excel(エクセル)DATEVALUE関数の使い方|文字列を日付シリアル値に変換する
使用関数:DATEVALUE
DATEVALUE(デイトバリュー)関数とは?
Excelでは1900年1月1日を「1」として1日1ずつ加算されていくシリアル値で日付を管理しています。2022年2月22日のシリアル値は44614で、この数値を日付計算に使います。44614に7を足し算すれば2022年3月1日になるわけです。
しかし、他のソフトなどからペーストされた日付文字列がExcelに「日付」として認識されない場合があります。「日付」でないデータはシリアル値を持たないので計算やデータの並べ替えに使えません。これをExcelの「日付」に変換してシリアル値を付与し、計算にも使えるようにする関数がDATEVALUEです。
DATEVALUE関数の引数と書式
「数式」タブ「関数ライブラリ」→「日付/時刻」→ DATEVALUEをクリックします。
DATEVALUEの引数は「日付文字列」です。
書式の構成はこうなります。
DATEVALUE関数で日付文字列をシリアル値に変換する
DATEVALUE関数の引数ダイアログを使って、日付文字列をシリアル値に変換してみましょう。
「日付」に見えるデータが入力されていますが、左揃えになっているので文字列だと分かります。これをDATEVALUE関数でシリアル値に変換します。
変換されたシリアル値の入るセルを選択してDATEVALUE関数の引数ダイアログを開き、「日付文字列」に最初の文字列のセルをクリックで指定します。
他のセルにもオートフィルコピーしてシリアル値が取得されましたが、最後の行だけはエラー#VALUE!が返りました。
DATEVALUE関数でエラー#VALUE!になる理由
- 令和3年1月4日 → 44200
- 2022-2-22 → 44614
- 2022年3月16日 → 44636
- 2021/4/5 14:20 → 44291
- R4.5.7 → 44688
- R3.6.19 20:00 → #VALUE!
日付に曜日や和暦での時刻の付加はエラー#VALUE!になる
前章の最終行がエラーになったのは「R3.6.19 20:00」が「日付を表す文字列」ではないからです。DATEVALUE関数では日付を表している文字列であるとExcelが判定したものだけが変換の対象になります。
多くの場合、末尾に付け足した時刻は無視されるだけで、シリアル値はちゃんと返ります。似たような表示の「2021/4/5 14:20」や「R4.5.7」は日付文字列と判定されるのに、なぜ「R3.6.19 20:00」がダメなのか悩むところですが、おそらく和暦の表示形式に付いた時刻は余計な文字と見なされるのでしょう。「20:00」を削除すればシリアル値は取得できます。
「曜日」を付け足した日付(例:2022/2/22 Tue)もエラー#VALUE!になります。これは西暦・和暦どちらもエラーです。
その他、引数に日付を直接入力する場合、ダブルクォーテーションで括るのを忘れるとエラーになります。
DATEVALUE関数で取得したシリアル値を日付に変換する
計算や並べ替えができると言っても、シリアル値の表示では日付を読み取れません。右クリック→「セルの書式設定」→「表示形式」→「日付」または「ユーザー定義」から好きな日付形式に変換しておきます。
- 日付と見える入力値でも文字列形式だと計算や並べ替えに使えません。DATEVALUE関数はこのような日付文字列をシリアル値に変換します
- 末尾に曜日の付いた日付は西暦・和暦とも「日付を表す文字列」ではないとExcelに判定されるためエラー#VALUE!が返ります
- 末尾に時刻の付いた日付は西暦では無視されますが、和暦では「日付を表す文字列」ではないとExcelに判定されるためエラー#VALUE!が返ります
- DATEVALUE関数で取得したシリアル値はセルの書式設定の表示形式で好きな日付形式に変換して使います