数値や文字列を日付のシリアル値に変換するExcel関数として、DATE関数またはDATEVALUE関数があります。Excel関数を使うと他人に数式の意味を説明しやすいというメリットがある一方で、関数の入れ子になるなど数式が複雑になりやすいというデメリットがあります。
数値を表す文字列に1を掛ける（または０を足す）と数値に変換することができますが、日付のシリアル値も数値なので同じように変換することができます（参考：【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ）。日付を表す文字列をシリアル値に変換するだけであれば、わざわざExcel関数を呼び出す必要はなく、1を掛けるなどの演算をするだけで良いです。
そこで、今回は日付を表す文字列をシリアル値に変換する方法について出題します。
目次
- １．はじめに
- ２．日付文字列をシリアル値に変換する
- ３．和暦の年月日を連結してシリアル値にする
- ４．年と月が変われば曜日も自動的に変わる
- ５．月日と年が逆になっているパターンの場合
- ６．TEXT関数を用いた変換について
１．はじめに
（１）日付のシリアル値について
Excelでは、日付を連番（シリアル値）で管理します。日付を入力するときはシリアル値を理解しなければなりません。日付のシリアル値について、詳しくはこちらの記事をご覧ください。
（２）文字列を数値に変換する
Excelでは、数値に見える文字列に1をかける（または０を足す）ことによって、数値に変換することができます。数値と文字列の違い、文字列を数値に変換する方法について、詳しくはこちらの記事をご覧ください。
２．日付文字列をシリアル値に変換する
（１）日付文字列に1を掛ける
問題
「="1/25"」という数式を入力した場合の計算結果はシリアル値と言えるか。また、シリアル値に変換して「yyyy年m月」の表示形式にしなさい。
解説
数式として「=1/25」と入力すると割り算になってしまい、計算結果は0.04となります。これは1月25日とは無関係です。
そこで、ダブルクォーテーションで囲みます。計算式でダブルクォーテーションで囲んだものは文字列として扱われます。文字列として入力されたものはシリアル値ではありません。したがって、この状態で表示形式を変えても、セルの表示は変わりません。
文字列として入力された日付をシリアル値にするには、足し算・引き算・掛け算・割り算の四則演算が必要です。最も簡単な方法は１を掛けることです。
１を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。なお、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。
これはシリアル値なので「yyyy年m月」の表示形式にすることができます。
別解
先頭にマイナスを2つ付ける方法もあります。マイナスのマイナスで、プラスになります。他にも０を足すとか、０を引くとかいろいろありますが、要するに四則演算があればよいです。
（２）月・日を分けて入力した場合
問題
月・日を分けて入力した日付はシリアル値と言えるか。また、シリアル値にして「yyyy年m月」の表示形式にしなさい。
解説
人間がこの表を見ると1月25日を表していることは分かりますが、Excelは2つの数字が並んでいるだけにしか見えません。月・日を分けて入力した場合、日付として認識されません。
そこで、スラッシュを入れて連結します。「=A2&"/"&B2」と入力します（参考：【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結）。
連結しただけの文字列は「文字列」です。文字列はシリアル値ではありません。
１を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。ただし、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。
「yyyy年m月」の表示形式にすることもできます。
別解
Excelで日付として認識される文字列はスラッシュだけではありません。例えば、ハイフンでもよいです。
この場合も単に連結しただけではシリアル値になりませんので、1を掛けます。
（３）年・月・日を分けて入力した場合
問題
1～10の数値を和暦にすることは可能か。また、年・月・日を用いてシリアル値を求め、和暦の日付の表示にしなさい。
解説
日にちとして1～10の数値を入力したとします。
これを和暦に変換すると明治33年になります。西暦1900年のことです。
表示形式を標準に戻します。
そこで、スラッシュを入れて連結します。年と月は絶対参照です。
連結しただけの文字列は「文字列」であり、シリアル値ではありません。文字列のままでは和暦になりません。１を掛けるとシリアル値になります。
和暦の表示形式にすることも可能です。
３．和暦の年月日を連結してシリアル値にする
問題
次の各設問について、平成25年2月5日のシリアル値を求め、西暦の日付にしなさい。
（１）和暦年2桁・月・日を入力した
（２）元号をT・S・H・Rで入力し、和暦年2桁と月を入力した
（３）元号を漢字で入力し、和暦年2桁・月・日を入力した
解説
2桁の年の場合、西暦の下2桁なのか和暦の2桁なのかが分かりません。先頭に「H」を連結します。さらに1を掛けてシリアル値にします。
イコールで参照します。
西暦の日付にすることもできます。
元号をセルに入力した場合はそれを参照します。日にちが無ければシリアル値を計算できないので、「/5」とします。さらに1を掛けてシリアル値にします。
これで昭和でも令和でも計算できます。
元号が漢字の場合、「ggge年m月d日」の形式にします。
４．年と月が変われば曜日も自動的に変わる
問題
セルA1の表示形式を文字列にして「2021年4月」と入力した。そして、4行目以降に1～30の連番を入力した。
2021年4月1日～30日の曜日を表示しなさい。また、2022年11月1日～30日の曜日にしなさい。
解説
まず、セルA1の表示形式を文字列にして「2021年4月」と入力します。
1～30の連番を入力していますが、これは単なる数値であり、日付でもシリアル値でもありません。
そこで、2021年4月1日のシリアル値を求めます。2021年4月を絶対参照にして、日にちと「日」を連結して2021年4月1日とします。さらに1を掛けます。
これを日付の表示形式にすれば日付になりますが、ユーザー定義でaaaにすれば曜日だけになります。
オートフィルをします。コピーします。これで30日までの曜日になります。
2022年11月にするとシリアル値も変わりますから、自動的に対応する曜日になります。
５．月日と年が逆になっているパターンの場合
（１）mm/dd/yyyy
問題
Excelで「12/01/2020」と入力しなさい。また、これをシリアル値に変換しなさい。
解説
欧米では、「12/01/2020」のように月日と年を逆にして表記することがあります。しかし、少なくとも日本語版のExcelではこの表記に対応しておらず、入力すると文字列として扱われます。入力すると表示形式は標準のままで左揃えになります。これはシリアル値ではありません。
これに1を掛けるとエラーになります。日付の文字列として認識されていないので、1を掛けるとエラーになってしまいます。
そこで、LEFT、RIGHT関数を用いて、年と月日を逆にして、スラッシュで連結します（参考：【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題）。
- =RIGHT(A1,4)&"/"&LEFT(A1,5)
連結した文字列に1を掛けてシリアル値に変換します。
- =(RIGHT(A1,4)&"/"&LEFT(A1,5))*1
（２）m/d/yyyy
問題
Excelで「3/1/2020」と入力しなさい。また、これをシリアル値に変換しなさい。
解説
1を掛けるとエラーになるのはさきほどの例と同じです。
そこで、LEFT、RIGHT、LEN関数を用いて、年と月日を逆にします。月日の文字数は全体の文字数から5文字を引きます（参考：Excelで特定の文字を検索して文字列の一部を抽出する）。
- =RIGHT(A1,4)&"/"&LEFT(A1,LEN(A1)-5)
連結した文字列に1を掛けてシリアル値に変換します。
- =(RIGHT(A1,4)&"/"&LEFT(A1,LEN(A1)-5))*1
６．TEXT関数を用いた変換について
8桁の数値を日付のシリアル値に変換するにはTEXT関数を使います。例えば、セルA1に8桁の数「20230405」を入力して2023/4/5を表すシリアル値に変換するには「=TEXT(A1,"#-00-00")*1」と入力します。
詳しくはこちらの記事をご覧ください。
解説は以上です。
