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

Excelの基本操作についての基礎と応用を入門解説
最終更新日: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 ・・・新着記事一覧を見る

PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)
IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



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

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


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




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