わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】TEXT関数で数値を日付や時刻のシリアル値に変換する、日付の変換方法まとめ

TEXT関数と言えば、数値の表示形式を変える関数なので、日付や時刻とは何の関係もないように見えます。しかし、Excelにおいて、日付や時刻の実体は数値(シリアル値)であり、しかも日付の表示形式(書式記号)もあるため、日付や時刻の変換は原則としてTEXT関数を使うべきなのです。

しかし、そのことを全く理解していないのか、日付を曜日に変換するだけなのにわざわざWEEKDAY関数やCHOOSE関数を使うといった意味不明な解説をしているサイトが散見されます。勉強不足と言っても過言ではありません。

そこで、今回は、TEXT関数を用いて、日付や時刻のシリアル値を数値に変換する方法、数値を日付や時刻のシリアル値に変換する方法、元号や曜日を取り出す方法などについて出題します。

 

 

目次

0.TEXT関数の基本(復習)

TEXT関数の基本と、数値を文字列に変換する方法についてはこちらの記事をご覧ください。

 

1.日付・時刻の変換の基本

(1)年・月・日を求める

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて年・月・日の部分を取り出しなさい。また、「2023年4月5日」にしなさい。

f:id:waenavi:20191029113819j:plain

 

解説

年・月・日は通常、YEAR、MONTH、DAY関数を使って求めますが、TEXT関数を使って求めることもできます。4桁の西暦年、月、日の書式記号はそれぞれ「yyyy」「m」「d」です(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

「=TEXT(A1,"yyyy")」「=TEXT(A1,"m")」「=TEXT(A1,"d")」と入力します。それぞれ2023、4、5となります。ただし、TEXT関数で取り出したものは文字列です。

f:id:waenavi:20191029113959j:plain

 

これらをくっつけて「2023年4月5日」とするには「=TEXT(A1,"yyyy年m月d日")」とします。

f:id:waenavi:20191029114039j:plain

 

別解

年月日はそれぞれYEAR、MONTH、DAY関数を使って求められるにもかかわらず、なぜTEXT関数を使うのかというと、表示形式の書式記号を別のセルに入力して、それを参照することができるからです。

f:id:waenavi:20191029114213j:plain

 

(2)曜日を求める

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて曜日を求めなさい。

f:id:waenavi:20191029113819j:plain

 

解説

日付から曜日を求めるには表示形式を使います。WEEKDAYではありません(参考:【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない)。Excel関数の場合はTEXT関数です。

曜日の書式記号は「aaa」です。「=TEXT(A1,"aaa")」と入力します。水曜日となります。ただし、TEXT関数を用いた時点でシリアル値ではなくなるので、日付としての計算はできなくなります。

f:id:waenavi:20191029114527j:plain

 

2.日付・時刻を数値に変換する

(1)年月日を8桁の数値に変換する

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて8桁の数「20230405」にしなさい。

f:id:waenavi:20200325104817j:plain

 

解説

年月日はyyyymmddです。「=TEXT(A1,"yyyymmdd")」と入力します。これで8桁の数になります。ただし、これは文字列です。

f:id:waenavi:20191029115934j:plain

 

数値化する場合は1を掛けます(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。

  • =TEXT(A1,"yyyymmdd")*1

f:id:waenavi:20191029120143j:plain

 

補足説明

1をかけたときに、日付の表示形式が適用されて########となることがあります。

f:id:waenavi:20191029120213j:plain

 

表示形式を「標準」にすれば数値になります。

f:id:waenavi:20191029120342j:plain

 

(2)時刻を4桁の数値に変換する

問題

セルB1に「12:34」と入力した。TEXT関数を用いて4桁の数「1234」にしなさい。

f:id:waenavi:20191029114615j:plain

 

解説

時間・分はhhmmです。「=TEXT(B1,"hhmm")*1」と入力します。表示形式を「標準」にすれば4桁の数になります。

f:id:waenavi:20191029120434j:plain

 

3.8桁の数値yyyymmddを日付のシリアル値に変換する

問題

セルA1に8桁の数「20230405」と入力した。TEXT関数を用いて文字列「2023年04月05日」にしなさい。また、シリアル値に変換して「2023/4/5」と表示しなさい。

f:id:waenavi:20191029120651j:plain

 

*補足*セルA1の表示形式は「標準」ですが、数値や文字列であっても問題ありません。また、先頭にシングルクォーテーションがあっても問題ありません。

 

解説

「2023年04月05日」とするには「=TEXT(A1,"#年00月00日")」とします。

f:id:waenavi:20191029120726j:plain

 

スラッシュで区切ります。「=TEXT(A1,"#!/00!/00")」と入力します。スラッシュで区切られましたが、これは文字列であってシリアル値(整数値)ではありません。なお、半角のスラッシュは直前に!が必要です。

f:id:waenavi:20191029120810j:plain

 

そこで1を掛けて数値化します。「45021」となります。スラッシュで区切られた文字列に1を掛けると数値化され、日付のシリアル値になります(参考:【Excel】日付を表す文字列に「1」を掛けるだけでシリアル値に変換できる)。

  • =TEXT(A1,"#!/00!/00")*1

f:id:waenavi:20191029120855j:plain

 

表示形式を日付にすると「2023/4/5」となります。

f:id:waenavi:20191029120925j:plain

 

別解

日付のシリアル値を求めるのであればスラッシュではなくハイフンでもよいです。「=TEXT(A1,"#-00-00")」と入力します。

*補足*半角のスラッシュは直前に!が必要ですが、半角のハイフンは不要です。

f:id:waenavi:20191029121738j:plain

 

ハイフンで区切られた文字列に1を掛けて数値化すると、日付のシリアル値になります。

  • =TEXT(A1,"#-00-00")*1

f:id:waenavi:20191029121849j:plain

 

表示形式を日付にします。

f:id:waenavi:20191029121853j:plain

 

4.4桁の数値hhmmを時刻のシリアル値に変換する

問題

セルA1に4桁の数「1234」と入力した。TEXT関数を用いてシリアル値に変換して「12:34」と表示しなさい。

f:id:waenavi:20191029122022j:plain

 

解説

「=TEXT(A1,"#!:00")」と入力します。「12:34」となりますが、これは文字列であってシリアル値(小数値)ではありません。

f:id:waenavi:20191029122059j:plain

 

そこで1を掛けて数値化します。「0.523611」となります。コロンで区切られた文字列に1を掛けると数値化され、時刻のシリアル値になります。

  • =TEXT(A1,"#!:00")*1

f:id:waenavi:20191029122157j:plain

 

表示形式を時刻にすると「12:34:00」となります。

f:id:waenavi:20191029122159j:plain

 

5.14桁の数値を日付+時刻に変換する

問題

セルA1に14桁の数「20230405123456」と入力した。TEXT関数を用いてシリアル値に変換して「2023/4/5 12:34:56」と表示しなさい。

f:id:waenavi:20191029122322j:plain

 

解説

20230405123456と入力します。13桁以上の数値は指数表示になります。

f:id:waenavi:20191029122340j:plain

 

表示形式を数値にします。

f:id:waenavi:20191029122430j:plain

 

「=TEXT(A23,"#-00-00 00!:00!:00")」と入力します。日付はハイフンでつなぎ、時刻はコロンでつなぎます。日付と時刻の間には半角のスペースが必要です。

f:id:waenavi:20191029122558j:plain

 

1を掛けて数値化します。「45021.52」となります。

f:id:waenavi:20191029122617j:plain

 

表示形式を「yyyy/m/d h:mm:ss」にすると「2023/4/5 12:34:56」となります。

f:id:waenavi:20191029122653j:plain

 

6.年月日から月日のみ抽出

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて文字列「0405」にしなさい。また、数値に変換して「405」と表示しなさい。

f:id:waenavi:20191029113819j:plain

 

解説

日付から月日だけ抽出して4桁で表示するには「=TEXT(A1,"mmdd")」とします。

f:id:waenavi:20200324084700j:plain

 

1を掛けて数値化します。「405」となります。

f:id:waenavi:20200324084819j:plain

 

7.和暦

(1)和暦を西暦に変換する

問題

セルA1に「H300303」と入力した。TEXT関数を用いて、2018/3/3(H30.3.3)のシリアル値にしなさい。

f:id:waenavi:20191029122846j:plain

 

解説

「H300303」は文字列なのでこのままではTEXT関数を使うことができません。先頭の文字と数字に分けて考えます。右の数字は「=RIGHT(A1,6)」です(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。

f:id:waenavi:20191029123224j:plain

 

ハイフンで分割します。「30-03-03」となります。

  • =TEXT(RIGHT(A1,6),"#-00-00")

f:id:waenavi:20191029123307j:plain

 

ところで、TEXT関数の第1引数は数値でなければなりませんが、RIGHT関数の答えは文字列です。しかし、数値化できる文字列は1を掛けなくてもTEXT関数の第1引数として使用することができ、自動的に数値化されます。したがって、1を掛ける必要はありません。

f:id:waenavi:20191029123516j:plain

 

先頭の文字は「LEFT(A1)」です。

  • =LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00")

f:id:waenavi:20191029123656j:plain

 

これは文字列であり、シリアル値ではありません。全体に1を掛けます。

  • =(LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00"))*1

f:id:waenavi:20191029123745j:plain

 

日付の表示形式にします。

f:id:waenavi:20191029123834j:plain

 

TEXT関数で「#-00-00」の形式に変換して、1を掛けることでシリアル値にすることができます。この他、「平成30年03月03日」のような日本語も1を掛けることでシリアル値にすることができます。

f:id:waenavi:20191029124316j:plain

 

(2)TEXT関数の入れ子

問題

セルA1に「20180303」と入力した。TEXT関数を用いて「H300303」にしなさい。

f:id:waenavi:20191029124428j:plain

 

解説

いったんハイフンで区切ります。

  • =TEXT(A1,"#-00-00")

f:id:waenavi:20191029124531j:plain

 

7桁の和暦の表示形式は「geemmdd」です。TEXT関数の第1引数に入れることによってシリアル値になり、geemmddによって7桁の和暦の形式になります。

  • =TEXT(TEXT(A1,"#-00-00"),"geemmdd")

f:id:waenavi:20191029124641j:plain

 

*補足*日付の形式で1をかけるとシリアル値になる文字列は、1をかけなくてもTEXT関数の第1引数にすることができます。

 

(3)元号の判定

問題

A列の日付が平成ならば〇をつけなさい。

f:id:waenavi:20191029124931j:plain

 

解説

「=TEXT(A1,"g")」と入力します。「g」は元号の書式記号です。昭和はS、平成はH、令和はRになります。

f:id:waenavi:20191029125026j:plain

 

これを利用してIF関数で元号の判定をすることができます。平成の日付だけ〇になります。

  • =IF(TEXT(A1,"g")="H","〇","")

f:id:waenavi:20191029125126j:plain

 

(4)元年の表示にする

問題

A列の日付を「平成e年m月d日」の表示形式にしなさい。ただし1年の場合は元年とする

f:id:waenavi:20191029132649j:plain

 

解説

「=TEXT(A1,"ggge年m月d日")」と入力します。元年が1年になります。表示形式だけで元年の表示にすることはできません。

f:id:waenavi:20191029132718j:plain

 

「=TEXT(A1,"e")」は和暦にした時の年になります。

f:id:waenavi:20191029132849j:plain

 

これが1の時は「元年」とします。1を引いて0になれば元年です。

  • =TEXT(A1,"ggg"&IF(TEXT(A1,"e")-1,"e","元")&"年m月d日")

f:id:waenavi:20191029132823j:plain

 


解説は以上です。


 

 


Copyright(C)2018-2020 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ