エクセル TEXT関数で日付を好きな文字列に変換する

シェアする

エクセルで日付を扱うには、セルに日付を入力するだけで事足ります。表示形式を変えたいときはセルの書式設定から選択することができます。

しかし、日付を含む文字列を作成したい場合、
書式設定からやるのは正しいやり方ではありません。

たとえば、「期限:2017年11月30日」というように、
「期限」 という文字を書式設定によって付加することは、
日付の書式設定とはいえず、適切ではありません。

このような場合、TEXT関数を使って日付を整形した文字列を作成することができます。

TEXT関数を使った日付の整形

エクセルでは、セルに日付を入れると、勝手に日付と認識してくれます。

例えば 1/29と入力すれば、勝手に、2017年1月29日と認識されます。
パソコンによって、デフォルト表示が違うため、
パソコンによっては、最初は「1月29日」となっているかもしれません。

セルの書式設定での日付の整形

この表示も、セルの書式設定を変更することで自由に変えられます。例えば、201701という「年」と「月」をつなげた「年月表示」にしようと思えば、セルを右クリックして、「セルの書式設定」から、「ユーザ定義」を選択し、「yyyymm」と入力することで可能になります。

セルの書式設定による「年月表示」

図:セルの書式設定による「年月表示」

でも、これだと、自由に日付から値を取得することができませんね。

TEXT 関数を使った日付整形

これに対し、TEXT関数を使うと、日付の入ったセルを参照して、
年、月、日などを自由に取り出すことができ、文言を作ることができます。

使い方を説明します。

TEXT関数: 値に対し、表示形式で指定した文字列を返答する関数です。

=TEXT(値, 表示形式)

というように使います。

日付で関連するものを、以下の表にまとめます。

TEXT関数 記号の意味(日付関連のみ)
書式記号 意味
#

1桁の数値を意味します。指定した桁数より少ない場合は、
#が無視され、余計な空白や数字でパディングされません。

例:=TEXT(1.5, “####.####”)
表示:「1.5」

※ 上記例の “&” は、文字列を結合する演算子です。
この例では、かぎかっこでくくるために使ってます。

0

1桁の数値を意味します。指定した桁数より少ない場合は、
先頭に 0 でパディングされます。

例:=TEXT(1.5, “0000.0000”)
表示:「0001.5000」

hh
h

時刻の「時」を表します。hh と重ねると、0~9時のとき、
二桁になるように先頭に 0 でパディングされます。

例と表示結果:

mm
m
(分)

時刻の「分」を表します。mm は 0パディングの二桁表示、
mは、そのまま1~2桁表示です。
※要注意!!!!(h か hh が前にないと日にちとみなされます。)

例:

下のように、表示形式の中で、h が出てくるまでは、
m も mm も 「月」を取得しています。
そして、h が一度出た後は、m(mmもでした)は、
「分」を意味します。
そして、h のあと、2回目以降の mm, m は、また、
月を示しています。

分だけを取り出す安全な別案

分だけで表示したい場合、TEXT の m だけでは実現できません。
日時変数より、分を数値として取り出す MINUTE関数を使うと
単独で取得できます。
数値で取り出しているため、
TEXT で数値を文字列に変換する必要があります。

以下の例では、0パディングの2桁の文字列を生成させてます。

=== 以下 余談です ===

ちなみに、全く別のVBAの関数の話ですが、
Format関数という、TEXT関数とよくにた役割をもつ
表示整形関数があります。
Format関数では、分を「n」で表示するようになっています。
そっちのほうがわかりやすい!!!

ss
s

秒の取得を表します。

例:

ssが0パディングあり、s が一桁のまま表示されてます。

これも余談ですが、

表示形式の文字列に、
何でもかんでも書式記号以外(と自分が思っている)
文字を入れることは、
それが、何かの意味を持つ可能性があるため、
少し危険です。

上図の例では、「、」と「。」が、
勝手にカンマとピリオドに変換されてますね。

 

面倒ですが、もし、意図しない出力結果になったら、
「&」で文字列を連結する方法に変えるのが、
安全ですね。

例:=text(d11,”ss”) & “秒です、” & text(d11,”s”) & “秒です。”

yyyy
yy

西暦を表します。yyyyは4桁表示、yyは2桁表示です。

mm
m
(月)

大体の場所、h が発生した直後のmmまたはmでなければ、
日付の月を示します。

「分」と同じ例ですが:

dd
d

日付の「日」を示します。同様ですが、
dd が0パディング付きの二桁表示、dが1~2桁表示です。

例:

他にも、いくつかありますが、よく使うのは上記の表ですね。

ほかは、簡単に書いておきます。

書式記号 意味
e

和暦の年表示

ggg

和暦の元号表示

mmm

月を英語の短縮形で表示

mmmm

月を英語で表示

aaa

曜日を短縮形で表示

aaaa

曜日を表示

上の表をまとめて、例です:

応用例

請求書番号を生成しているコードです。

※ 微妙なのは、「分」を意味するm だけ。

日付を「書式設定」に頼らない表示。

書式設定でも、同じ表示はできるのですが、

書式設定でこれをやると、どういう設定なのかぱっと確認できないですし、
あくまで、データは、日付型になってしまうので、

TEXT を使って文字列に変換して、「&」で 文字列を連結して
文面を作成するほうが理にかなっていると思います。

まとめ

TEXT関数を使って、日付から、月 を取得したり、日 を取得したりできるようになると、
日付に関連した数字を使った文書を簡単に作ることができます。

ぜひ、ご活用ください。

この記事が気に入ったら
いいね ! しよう