TEXT関数と言えば、数値の表示形式を変える関数なので、日付や時刻とは何の関係もないように見えます。しかし、Excelにおいて、日付や時刻の実体は数値（シリアル値）であり、しかも日付の表示形式（書式記号）もあるため、日付や時刻の変換は原則としてTEXT関数を使うべきなのです。
しかし、そのことを全く理解していないのか、日付を曜日に変換するだけなのにわざわざWEEKDAY関数やCHOOSE関数を使うといった意味不明な解説をしているサイトが散見されます。勉強不足と言っても過言ではありません。
そこで、今回は、TEXT関数を用いて、日付や時刻のシリアル値を数値に変換する方法、数値を日付や時刻のシリアル値に変換する方法、元号や曜日を取り出す方法などについて出題します。
目次
- ０．TEXT関数の基本（復習）
- １．日付・時刻の変換の基本（復習）
- ２．日付・時刻を数値に変換する
- ３．8桁の数値yyyymmddを日付のシリアル値に変換する
- ４．4桁の数値hhmmを時刻のシリアル値に変換する
- ５．14桁の数値を日付＋時刻に変換する
- ６．年月日から月日のみ抽出
- ７．和暦
０．TEXT関数の基本（復習）
TEXT関数の基本と、数値を文字列に変換する方法についてはこちらの記事をご覧ください。
１．日付・時刻の変換の基本（復習）
（１）年・月・日を求める
問題
セルA1に「2023/4/5」と入力した。TEXT関数を用いて年・月・日の部分を取り出しなさい。また、「2023年4月5日」にしなさい。
解説
年・月・日は通常、YEAR、MONTH、DAY関数を使って求めますが、TEXT関数を使って求めることもできます。4桁の西暦年、月、日の書式記号はそれぞれ「yyyy」「m」「d」です（参考：【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ）。
「=TEXT(A1,"yyyy")」「=TEXT(A1,"m")」「=TEXT(A1,"d")」と入力します。それぞれ2023、4、5となります。ただし、TEXT関数で取り出したものは文字列です。
これらをくっつけて「2023年4月5日」とするには「=TEXT(A1,"yyyy年m月d日")」とします。
別解
年月日はそれぞれYEAR、MONTH、DAY関数を使って求められるにもかかわらず、なぜTEXT関数を使うのかというと、表示形式の書式記号を別のセルに入力して、それを参照することができるからです。
（２）曜日を求める
問題
セルA1に「2023/4/5」と入力した。TEXT関数を用いて曜日を求めなさい。
解説
日付から曜日を求めるには表示形式を使います。WEEKDAYではありません（参考：【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない）。Excel関数の場合はTEXT関数です。
曜日の書式記号は「aaa」です。「=TEXT(A1,"aaa")」と入力します。水曜日となります。ただし、TEXT関数を用いた時点でシリアル値ではなくなるので、日付としての計算はできなくなります。
２．日付・時刻を数値に変換する
（１）年月日を8桁の数値に変換する
問題
セルA1に「2023/4/5」と入力した。TEXT関数を用いて8桁の数「20230405」にしなさい。
解説
年月日はyyyymmddです。「=TEXT(A1,"yyyymmdd")」と入力します。これで8桁の数になります。ただし、これは文字列です。
数値化する場合は1を掛けます（参考：【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ）。
- =TEXT(A1,"yyyymmdd")*1
補足説明
１をかけたときに、日付の表示形式が適用されて＃＃＃＃＃＃＃＃となることがあります。
表示形式を「標準」にすれば数値になります。
（２）時刻を4桁の数値に変換する
問題
セルB1に「12:34」と入力した。TEXT関数を用いて4桁の数「1234」にしなさい。
解説
時間・分はhhmmです。「=TEXT(B1,"hhmm")*1」と入力します。表示形式を「標準」にすれば4桁の数になります。
３．8桁の数値yyyymmddを日付のシリアル値に変換する
問題
セルA1に8桁の数「20230405」と入力した。TEXT関数を用いて文字列「2023年04月05日」にしなさい。また、シリアル値に変換して「2023/4/5」と表示しなさい。
＊補足＊セルA1の表示形式は「標準」ですが、数値や文字列であっても問題ありません。また、先頭にシングルクォーテーションがあっても問題ありません。
解説
「2023年04月05日」とするには「=TEXT(A1,"#年00月00日")」とします。
スラッシュで区切ります。「=TEXT(A1,"#!/00!/00")」と入力します。スラッシュで区切られましたが、これは文字列であってシリアル値（整数値）ではありません。なお、半角のスラッシュは直前に！が必要です。
そこで1を掛けて数値化します。「45021」となります。スラッシュで区切られた文字列に1を掛けると数値化され、日付のシリアル値になります（参考：【Excel】日付を表す文字列に「１」を掛けるだけでシリアル値に変換できる）。
- =TEXT(A1,"#!/00!/00")*1
表示形式を日付にすると「2023/4/5」となります。
別解
日付のシリアル値を求めるのであればスラッシュではなくハイフンでもよいです。「=TEXT(A1,"#-00-00")」と入力します。
＊補足＊半角のスラッシュは直前に！が必要ですが、半角のハイフンは不要です。
ハイフンで区切られた文字列に1を掛けて数値化すると、日付のシリアル値になります。
- =TEXT(A1,"#-00-00")*1
表示形式を日付にします。
４．4桁の数値hhmmを時刻のシリアル値に変換する
問題
セルA1に4桁の数「1234」と入力した。TEXT関数を用いてシリアル値に変換して「12:34」と表示しなさい。
解説
「=TEXT(A1,"#!:00")」と入力します。「12:34」となりますが、これは文字列であってシリアル値（小数値）ではありません。
そこで1を掛けて数値化します。「0.523611」となります。コロンで区切られた文字列に1を掛けると数値化され、時刻のシリアル値になります。
- =TEXT(A1,"#!:00")*1
表示形式を時刻にすると「12:34:00」となります。
５．14桁の数値を日付＋時刻に変換する
問題
セルA1に14桁の数「20230405123456」と入力した。TEXT関数を用いてシリアル値に変換して「2023/4/5 12:34:56」と表示しなさい。
解説
20230405123456と入力します。13桁以上の数値は指数表示になります。
表示形式を数値にします。
「=TEXT(A23,"#-00-00 00!:00!:00")」と入力します。日付はハイフンでつなぎ、時刻はコロンでつなぎます。日付と時刻の間には半角のスペースが必要です。
1を掛けて数値化します。「45021.52」となります。
表示形式を「yyyy/m/d h:mm:ss」にすると「2023/4/5 12:34:56」となります。
６．年月日から月日のみ抽出
問題
セルA1に「2023/4/5」と入力した。TEXT関数を用いて文字列「0405」にしなさい。また、数値に変換して「405」と表示しなさい。
解説
日付から月日だけ抽出して4桁で表示するには「=TEXT(A1,"mmdd")」とします。
1を掛けて数値化します。「405」となります。
７．和暦
（１）和暦を西暦に変換する
問題
セルA1に「H300303」と入力した。TEXT関数を用いて、2018/3/3（H30.3.3）のシリアル値にしなさい。
解説
「H300303」は文字列なのでこのままではTEXT関数を使うことができません。先頭の文字と数字に分けて考えます。右の数字は「=RIGHT(A1,6)」です（参考：【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題）。
ハイフンで分割します。「30-03-03」となります。
- =TEXT(RIGHT(A1,6),"#-00-00")
ところで、TEXT関数の第1引数は数値でなければなりませんが、RIGHT関数の答えは文字列です。しかし、数値化できる文字列は1を掛けなくてもTEXT関数の第1引数として使用することができ、自動的に数値化されます。したがって、1を掛ける必要はありません。
先頭の文字は「LEFT(A1)」です。
- =LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00")
これは文字列であり、シリアル値ではありません。全体に1を掛けます。
- =(LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00"))*1
日付の表示形式にします。
TEXT関数で「#-00-00」の形式に変換して、1を掛けることでシリアル値にすることができます。この他、「平成30年03月03日」のような日本語も１を掛けることでシリアル値にすることができます。
（２）TEXT関数の入れ子
問題
セルA1に「20180303」と入力した。TEXT関数を用いて「H300303」にしなさい。
解説
いったんハイフンで区切ります。
- =TEXT(A1,"#-00-00")
7桁の和暦の表示形式は「geemmdd」です。TEXT関数の第1引数に入れることによってシリアル値になり、geemmddによって7桁の和暦の形式になります。
- =TEXT(TEXT(A1,"#-00-00"),"geemmdd")
＊補足＊日付の形式で1をかけるとシリアル値になる文字列は、1をかけなくてもTEXT関数の第1引数にすることができます。
（３）元号の判定
問題
A列の日付が平成ならば〇をつけなさい。
解説
「=TEXT(A1,"g")」と入力します。「g」は元号の書式記号です。昭和はS、平成はH、令和はRになります。
これを利用してIF関数で元号の判定をすることができます。平成の日付だけ〇になります。
- =IF(TEXT(A1,"g")="H","〇","")
（４）元年の表示にする
問題
A列の日付を「平成e年m月d日」の表示形式にしなさい。ただし1年の場合は元年とする。
解説
「=TEXT(A1,"ggge年m月d日")」と入力します。元年が1年になります。表示形式だけで元年の表示にすることはできません。
「=TEXT(A1,"e")」は和暦にした時の年になります。
これが1の時は「元年」とします。1を引いて0になれば元年です。
- =TEXT(A1,"ggg"&IF(TEXT(A1,"e")-1,"e","元")&"年m月d日")
解説は以上です。
