【関数】日付の表示形式を指定する（セルの書式設定/TEXT関数）

日付は、Excelで取り扱いにクセのある代表格のひとつです。

その日付の表示形式を変更する方法は、文字列での表示を除いて主に２通りあります。

ひとつはセルの書式設定を利用する方法、もうひとつはTEXT関数を使う方法です。

このページでは、セルの書式設定とTEXT関数を使った日付の表示形式について徹底解説します。

セルの書式設定とTEXT関数について

セルの書式設定について

Excelで日付を入力した場合、通常は入力した日付の形式に合わせてセルが勝手に書式を調整してくれます。

とはいえ、何らかの理由でうまく書式が設定できない場合や、あらかじめ表示形式を指定しておきたい場合は、セルの書式設定から表示形式を指定することになります。

指定と言っても、基本的には分類から「日付」を選択し、種類を選ぶだけのことです。

これだけでも事足りることは多いと思いますが、元々用意されている選択肢では曜日が表示できなかったり、全角表示できなかったりと、ある程度体裁を整えようとすると不足を感じるようになります。

そういったとき、分類の「ユーザー定義」で、書式記号を入力することでより自由度の高い表示形式を作成することができます。

例えば次の図は入力した日付を指定の表示形式に変換する例です。セルには日付をアメリカ式で表示するための表示形式を「ユーザー定義」に設定してあります。

このように「ユーザー定義」を活用することで、柔軟な表示を自由自在に設定することができます。

TEXT関数について

TEXT関数は、数値や日付などの表示形式を指定できる関数で、構成は次のとおりです。

=TEXT(値,”表示形式”)

使い方は後にサンプルを交えながら具体的に記載しますが、例えば次のようなイメージです。

これでセルの表示は「July 3, 2021」となります。

書式記号はセルの書式設定の場合と基本的に同じですが、細かい部分で指定方法が若干異なる点に注意が必要です。

TEXT関数は数値や時刻などの表示形式にも利用できます。

Excelの日付について

シリアル値とは

Excelの日付は文字列形式のものを除いて、すべてシリアル値と呼ばれる数値を日付の形式で表現したものです。

そのため、Excelの日付の取り扱いに関して、シリアル値というものを何となくでも知っているかどうかが大きな差になります。

と言うと少し難しいもののように感じますが、いたってシンプルです。

単に1900年1月1日を「１」とし、１日ごとに１ずつ加算されていくただの数値とだけ思っておいていただければじゅうぶんです。

Windows版と旧Mac版の日付システムの違い

余談ですが、このシリアル値について、昔はWindows版とMac版で採用していた日付システムが異なっていたというのは有名な話です。

このように、４年間もずれる日付システムを採用していたわけです。

そのため、かつてはWindows版とMac版の間でファイルを共有すると、日付がずれる互換性問題を抱えており、その表示を合わせるための設定メニューが用意されていました。

しかし、Mac版Excel2011からは日付システムをWindows版と同じ1900日付システムに変更し、近年はそういった問題はなくなったようです。

ただ、日付システムが統一される前、つまりMac版Excel2008以前で作成されたファイルを、近年のバージョンで開くとやはり日付がずれるため、日付表示を合わせるための設定はまだ残っています。

この件については、Microsoftのページに「異なる日付システムで作成したBook間での日付コピーには注意が必要」という趣旨の記事が掲載されていますのでご参考ください。

（参考）Excel の 1900 年と 1904 年の日付システムの違い

1900日付システムは本来なかったはずの1900年2月29日が存在します。これについてはMicrosoftがバグではなく、日付の整合性を保つための仕様と説明しています。

文字列日付をシリアル値に変換する方法

セルの書式設定の部分でも記載したとおり、本来、Excelで日付を入力するとセルが勝手にシリアル値に変換してくれます。

しかし、セルの書式設定が「文字列」となっているセルではシリアル値への変換はしてもらえません。あくまで文字列として扱われます。

そういったとき、文字列の日付をシリアル値に変換する方法は２つあります。

１を掛けてシリアル値へ変換

１番簡単なのは、文字列日付のセルに１を掛けてシリアル値へ変換する方法です。

図の例ではB2～B10セルに文字列で日付が入力されています。（セルの書式設定を文字列に設定したあと日付を手入力した場合など）

そこで、となりのC2セルに「=B2*1」という数式を入力し、C10までオートフィルするとシリアル値に変換されます。

DATEVALUE関数でシリアル値へ変換

王道なのがDATEVALUE関数を使う方法です。

DATEVALUE関数は見てのとおり引数に文字列日付の入ったセルを指定するだけです。

図の例ではC2セルにDATEVALUEを入れ、C10までオートフィルすることでC列をシリアル値変換しています。

共通する注意点

上記の方法でシリアル値に変換するとき、次の２点には注意してください。

• 数式入力セルの書式設定が「文字列」でないこと
• 変換元の文字列日付が日付として認識できること

そのままの意味なので補足はいらないと思いますが、シリアル値に変換するための数式入力セル（図ではC列）の書式設定が文字列になっていると、入力した数式も当然ながら数式ではなく文字列として認識されてしまいます。

入力した数式がそのまま表示される場合は、セルの書式設定を開いて「標準」などに戻してください。

なお、変換元の文字列日付が、日付として認識できる形式で入力されていない場合、当然ながらエラーになります。

年/月/日/曜日の書式記号

日付の表示形式に関する書式記号を、年、月、日、曜日でそれぞれ一覧にしておきます。

年の書式記号

表示例	書式記号
2021	yyyy
21	yy
令和3	ggge
R3	ge
R03	gee
3	e
03	ee

月の書式記号

表示例	書式記号
7	m
07	mm
12	m または mm
Jul	mmm
July	mmmm
J	mmmmm

月が２桁になる１０～１２月に関しては、mの場合もmmの場合も結果は同じです。

また、あまり使う機会もなく知られていませんが、mmmなどで月の英語表記も可能です。

日の書式記号

表示例	書式記号
3	d
03	dd
11	d または dd

月のときと同様、２桁の日はdの場合もddの場合も結果は同じです。

曜日の書式記号

表示例	書式記号
土	aaa
土曜日	aaaa
sat	ddd
saturday	dddd

記載すべきか悩みましたが、日の書式記号である「d」を３～４個指定することで、曜日の英語表記ができます。

書式記号の指定方法

前段で記載した日付の「書式記号」は、セルの書式設定、TEXT関数とも基本的に共通しています。

しかし、一部指定方法はルールが微妙に異なるので注意が必要です。

“年”・”月”・”日”などのつなぎ方

表示例	書式設定	TEXT関数
2021年7月3日	yyyy”年”m”月”d”日”	“yyyy年m月d日”
2021/7/3	yyyy/m/d	“yyyy/m/d”
令和3年7月3日	ggge”年”m”月”d”日”	“ggge年m月d日”
R3.7.3	ge.m.d	“ge.m.d”

セルの書式設定に関して、明確な説明は見たことないのですが、全角文字列をつなぐ場合は”で”囲い、半角文字列はそのまま記載する形のようです。

全角・漢字表示などの指定

よく使う書式記号として[DBNum]というものがあります。

１～４まであり、それぞれ指定すると次のような表示にすることができます。

４つの指定方法

表示例	書式記号
令和三年七月三日	[DBNum1]
令和参年七月参日	[DBNum2]
令和３年７月３日	[DBNum3]
令和3年7月3日	[DBNum4]

DBNumの１～２は漢数字、３は全角、４は半角での表示になります。

指定例

セルの書式設定	TEXT関数
[DBNum1]ggge”年”m”月”d”日”	“[DBNum1]ggge年m月d日”

すべて書式記号の先頭部分に記載します。

なお、TEXT関数での全角半角については次のようにJIS関数（全角）、ASC関数（半角）で指定する方法もあります。

全角：=JIS(TEXT(TODAY,”ggge年m月d日”))

半角：=ASC(TEXT(TODAY,”ggge年m月d日”))

何も指定しない場合、基本は半角になるので、わざわざASC関数で半角を指定する機会はあまりないと思いますが、全角指定は役に立つシーンが多いのではないでしょうか。

書式記号テンプレート

よく使いそうな書式記号のテンプレートを記載しておきます。

ここに記載するパターンを参考にしてもらえれば、だいたいの表示形式は作れると思います。

なお、この中の一部（２つくらい）は、セルの書式設定の日付分類の中にもあるので、わざわざユーザー定義に記述する必要はないですが、他との比較のために一応記載しています。

セルの書式設定用

表示例	書式記号テンプレート（ユーザー定義）
令和3年7月3日	ggge”年”m”月”d”日”
令和３年７月３日	[DBNum3]ggge”年”m”月”d”日”
令和３年７月３日(土)	[DBNum3]ggge”年”m”月”d”日”(aaa)
令和３年７月３日（土）	[DBNum3]ggge”年”m”月”d”日（”aaa”）”
令和三年七月三日（土）	[DBNum1]ggge”年”m”月”d”日（”aaa”）”
R3.7.3	ge.m.d
R３.７.３	[DBNum3]ge.m.d
2021/7/3(土)	yyyy/m/d(aaa)
２０２１/７/３(土)	[DBNum3]yyyy/m/d(aaa)
２０２１／７／３（土）	[DBNum3]yyyy”／”m”／”d”（”aaa”）”
７月３日（土曜日）	[DBNum3]m”月”d”日（”aaaa”）”
７／３（土）	[DBNum3]m”／”d”（”aaa”）”
７月３日（土曜日）	[DBNum3]m”月”d”日（”aaaa”）”
July 3, 2021	mmmm d, yyyy
Jul/3/21	mmm/d/yy
7/3/21	m/d/yy
3 July 2021	d mmmm yyyy
3/Jul/21	d/mmm/yy
3/7/21	d/m/yy

TEXT関数用

表示例	書式記号テンプレート（第２引数）
令和3年7月3日	“ggge年m月d日”
令和３年７月３日	“[DBNum3]ggge年m月d日”
令和３年７月３日(土)	“[DBNum3]ggge年m月d日(aaa)”
令和３年７月３日（土）	“[DBNum3]ggge年m月d日（aaa）”
令和三年七月三日（土）	“[DBNum1]ggge年m月d日（aaa）”
R3.7.3	“ge.m.d”
R３.７.３	“[DBNum3]ge.m.d
2021/7/3(土)	“yyyy/m/d(aaa)
２０２１/７/３(土)	“[DBNum3]yyyy/m/d(aaa)
２０２１／７／３（土）	“[DBNum3]yyyy／m／d（aaa）”
７月３日（土曜日）	“[DBNum3]m月d日（aaaa）”
７／３（土）	“[DBNum3]m／d（aaa）”
７月３日（土曜日）	“[DBNum3]m月d日（aaaa）”
July 3, 2021	“mmmm d, yyyy”
Jul/3/21	“mmm/d/yy”
7/3/21	“m/d/yy”
3 July 2021	“d mmmm yyyy”
3/Jul/21	“d/mmm/yy”
3/7/21	“d/m/yy”

まとめ

セルの書式設定とTEXT関数の使い分け

日付の表示形式に関しては、セルの書式設定でも、TEXT関数でも、同じように表示できます。

しかし、セルの「設定」と「関数」はまったく性質の違うものなので、それぞれに特徴と使いどころがあります。

主だった特徴を挙げるなら次のとおりです。

方法	特徴
セルの書式設定	・直接入力できる。
TEXT関数	・数式への組み込みがしやすい。 ・値としてコピーすると表示形式を維持したまま値化できる。

少し補足すると、セルの書式設定で表示形式を指定したセルは当然ながら直接入力ができます。

この方法だと数式が消える心配はありませんし、入力値を確定するだけで指定した表示形式に変換できて便利です。

一方、TEXT関数では数式を入れたセルに直接入力すると数式が消えてしまうため、次の図のように入力セルと表示セルを別々に用意する、という手順が必要になります。

でも、TEXT関数なら次のように文面の一部に日付を組み込むことが可能です。

つまり、数式の中に組み込んだり、特定のセルの日付を参照して別のセルに日付を表示するような場合はTEXT関数、それ以外はセルの書式設定というような使い分けがおすすめです。

TEXT関数は単なる表示コントロール以外にも、例えば月だけを抽出してSUMIF関数の集計キーにしたり、IF関数、COUNTIF関数の条件にするなど、その他にも多くの使い道があります。

年を非表示にする時の注意点

「7/3」のように、年を表示しない場合は、シリアル値が意図した年になっているか注意が必要です。

昨年のファイルを使いまわしているようなケースでは、年（シリアル値）が昨年のままになっているかもしれません。

出納簿などでこのような状態のまま、SUMIF関数などで日付をキーにして集計をかけると思わぬ間違いが発生することになるので注意しましょう。

差支えなければ年も表示しておくのがおすすめです。