エクセル基本操作
日付、時刻に関するあれこれ(「元年」表記追加)

Excelの基本操作についての基礎と応用を入門解説
公開日:2013年5月以前 最終更新日:2019-02-04

日付、時刻に関するあれこれ(「元年」表記追加)


1.曜日の表示方法


2.数値を日付にする方法

3.24時以降(26時等)の表示書式

4.日付と日付の間隔(年齢等)の求め方、DATEDIFに関して

5.日付、時刻のシリアル値(数値)

6.和暦表示について「元年」表記」


1.曜日の表示方法

曜日の書式に関しては、「セルの書式設定」にサンプルが出ていない為、
知らない方が多いようです。
A B C
1 日付 曜日 曜日
2 4月1日 金曜日
3 4月2日 土曜日
4 4月3日 日曜日

方法1
B2のセルは、=TEXT(A2,"aaa")
C2のセルは、=TEXT(A2,"aaaa"
3行目以降も同様です。
方法2
B2のセルは、=A2、「セルの書式設定」-「ユーザー設定」で、「aaa」
C2のセルは、=A2、「セルの書式設定」-「ユーザー設定」で、「aaaa」
3行目以降も同様です。
また、4月1日(金)のように表示するには、
「セルの書式設定」-「ユーザー設定」で、「m"月"d"日"(aaa)」
日付の書式


書式記号 表示結果
yy 00~99(西暦の年)
yyyy 1900~9999(西暦の年)
e 1~99(和暦の年)
ee 01~99(和暦の年)
g H(元号のアルファベット)
gg 平(元号の1文字目)
ggg 平成(元号)
m 1~12(月の数字表記)
mm 01~12(月の数字表記)
mmm Jan~Dec(月の短縮英語表記)
mmmm January~December(月の英語表記)
d 1~31(日)
dd 01~31(日)
ddd sun~sat(曜日の短縮英語表記)
dddd Sunday~Satrday(曜日の英語表記)
aaa 日~土(曜日の日本語1文字表記)
aaaa 日曜日~土曜日(曜日の日本語表記)
上記以外にもありますが、以上が使えれば良いと思います。

2.数値を日付にする方法

セルA1が「20110420」の場合、A2に日付にする方法です。
以下のいくつかの方法があります。
  • =DATEVALUE(TEXT(A1,"0000!/00!/00"))
  • =TEXT(A1,"0000!/00!/00")*1・・・(*1は、+0でも同じです)
  • =TEXT(A1,"0000-00-00")*1
  • =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
DATEVALUEは、文字列としての日付を日付にする関数です。
DATE(年,月,日)で日付を返す関数です。

TEXT関数の戻り値は、文字列なので、これを日付にする必要があります。
考え方としては、DATEVALUEが素直ではありますが、
後述するように、エクセルでは日付は数値として扱っていますので、*1や+0でも同じ結果になります。
(エクセルは、文字列でも内容が数値であれば計算が可能です、いろいろな場面で活用できる技です。)

3.24時以降(26時等)の表示書式



A B C D
1 14:00 12:00 2:00 26:00
C2=A1+A2、D1=A1+B1

書式設定をしない場合は、C1のように、2:00となります。
これは、C1の書式が「h:mm」であり、この「h」は0~23です。

D1には、セルの書式で、「[h]:mm」の書式が設定されています。
[h]は、24時以降の時刻を表示します。([hh]等も可能です。)

A1+B1の結果は、1日と2時間なのです。
つまり書式で、「d"日" h:mm」と設定すれば、「1日 2:00」

※エクセルでは、時間(時刻と時刻の間隔)とは考えません、あくまで時刻なのです。

4.日付と日付の間隔(年齢等)の求め方、DATEDIFに関して

A B C
1 1970/4/1 2011/4/20 41年 0か月 19日
セルC1は、A1とA2の間隔です。

計算式は、
C2=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),DAY(B1)<DAY(A1))),1,0)&"年 "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)>=DAY(A1)),12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(A1)),-1)))&"か月 "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&"日"
これは、マイクロソフト サポート オンラインで紹介されているものです。
かなり面倒な数式です。

実は、DATEDIFという関数があります、ただし関数ウイザードには出てきません。
しかし、この関数は、日付と日付の間隔を求める為の関数です。
この関数を使用すると、
C2=DATEDIF(A1,B1,"y") & "年 " & DATEDIF(A1,B1,"ym") & "か月 " & DATEDIF(A1,B1,"md") & "日 "
となり、遥かに簡単です。
でも、MSは使ってほしくないようです。
なぜかというと、計算結果にバグがある身体と思われます。
特定の条件下で、"yd"や"md"に誤りがあることが分かっています。
特定の条件というのは、うるう年の2/29を跨ぐような日付間のことです。

しかし、
年齢計算"y"や月数"m"においては、これが問題となることがありませんので、
"yd"や"mdは使わないほうが良いのですが、使うとしても同一年内等に限定して使うようにしてください。

また、年齢計算において、
YEARFRAC関数を使用していることがありますが、
小数以下の処理において、かえって煩雑であり、かつ確実な方法は難しいようです。
年齢計算は、DATEDIF関数を使うのが最も簡単かつ適切だと思われます。

5.日付、時刻のシリアル値(数値)

エクセルの日付は、
1900/1/1を基準にした数値です。
1900/1/1が1です。
つまり1900/1/3は3です。

そして、時刻は、
1時=1÷24(時間)=0.0416666666…
1分=1÷24(時間)÷60(分)=0.000694444…
1秒=1÷24(時間)÷60(分)÷60(秒)=0.000011574…
になります、この数値自体に意味は有りません。
ただ結果として、小数以下の適当な数値を入力しても、近似値の時刻で表示することができます。

2011/4/20 10:10:10は、40634.4237268519です。
40634が2011/4/20で、0.4237268519が10:10:10です。
ちなみに、数値0は、1900/1/0 0:0:0になってしまいます。


6.和暦表示について「元年」表記」

和暦表示する場合には、「セルの書式設定」で、

エクセル セルの書式設定

分類:日付
カレンダーの種類:和暦
種類:平成24年3月14日 ・・・ この日付はExcelのバージョンで変わります。
これで、
平成31年2月3日
このような表示になります。

または、
分類:ユーザー定義
種類:ggge"年"mm"月"dd"日"
これで、
平成31年02月03日
このような表示になります。

しかし、
1989/2/3 の場合、
平成1年2月3日 と表示されます。
これを 平成元年年2月3日 と表示したい場合、
「セルの書式設定」でやろうとすると、かなり面倒な指定をしなければなりません。
日付のシリアル値を判定して、元年の場合とそれ以外の書式を設定する必要があります。
分類:ユーザー定義
種類:[<=32515]ggge"年"m"月"d"日";[>=32874]ggge"年"m"月"d"日";ggg"元年"m"月"d"日"
こんなに長くて、しかもこれは「平成」のみの対応です。
(やっているのは、平成元年のシリアル値とそれ以外の書式を変えているだけです)
昭和元年に対応できていないし、ましてや新元号の対応などこれでは不可能です。
(これを書いているのは平成31年2月です。)

もっと簡単で、全ての元号に対応する方法があります。

条件付き書式を使います。
以下は、A列に設定することを例にしています。
A列を選択(A1がアクティブ)して、「新しいルール」

エクセル 「元年」表示

ルールに、
=TEXT(A1,"e")="1"
そして、「書式」で、

エクセル 「元年」表示

種類に、
ggg"元年"m"月"d"日"

これで、以下のようになります。

エクセル 「元年」表示

このように、昭和も平成も「元年」と表示されます。
そして、「新元号になっても、このままで自動的に対応されます。
※Microsoftがアップデートで新元号対応することが前提です。
 この対応が遅れたときは、「元年」表記どころの騒ぎでは済みませんので割愛します。




同じテーマ「エクセル基本操作」の記事

日付、時刻に関するあれこれ(「元年」表記追加)
数値の書式あれこれ
関数入力のショートカット
ひらがな⇔カタカナの変換
セルの表示形式のショートカット
可視セルのみコピーする(非表示の行・列を除く)
日付表示の桁位置を揃える
覚えておきたいエクセルの操作:Enter編
覚えておきたいエクセルの操作:入力・編集
覚えておきたいエクセルの操作:ジャンプ
覚えておきたいエクセルの操作:数式のコピー


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ