エクセル基本操作 | 日付、時刻に関するあれこれ | Excelの基本操作についての基礎と応用を入門解説



最終更新日:2014-05-10

日付、時刻に関するあれこれ


1.曜日の表示方法
2.数値を日付にする方法
3.24時以降(26時等)の表示書式
4.日付と日付の間隔(年齢等)の求め方、DATEDIFに関して
5.日付、時刻のシリアル値(数値)。


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"に誤りがあることが分かっています。

しかし、年齢計算"y"や月数"m"においては問題がありませんので、通常使用においては問題ないはずです。

また、年齢計算において、
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になってしまいます。




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

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

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

空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.定数と型宣言文字(Const)|ExcelマクロVBA入門



  • >
  • >
  • >
  • 日付、時刻に関するあれこれ

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


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




    ↑ PAGE TOP