表計算ソフトで時間計算、日数計算を行うための7つのレッスン　第4回

■■Lesson 4
「シリアル値を自在に操る」

[ここで学ぶ関数 DATE() TIME() DATEDIF() ]

■ シリアル値と年・月・日・時・分・秒との相互変換

◆ 年・月・日・時・分・秒からシリアル値へ

（西暦）年、月、日、時、分、秒を表す特定の数値（通常の10進法）を時間（シリアル値）に変換するには、
DATE( [年] , [月] , [日] ) 関数、TIME( [時] , [分] , [秒] ) 関数を使う。

たとえば、C1に「月」（1〜12)を入力すると、西暦2020年のその月の16日にあたる日付を表示する数式は以下のとおり。

=DATE(2020,C1,16)

この数式で、C1に13以上の数値を入力すると、翌年以降の該当する日付に変換されて表示される。たとえば、13の場合であれば、このセルの値は 2021/1/16 になる。

補足：
="2020/"&C1&"/16"
のようにセルの値と文字列を「&」でつなげて直接、文字列にしてしまうという荒技もある。
この場合、13以上などイレギュラーな値だと、日付ではないふつうの文字列として認識される可能性があるので注意。
Lesson5 で解説する DATEVALUE() を使うとうまくいくケースもある。
たとえば、Excel では
=DATEVALUE(C1&"/1/16")
とすると、C1 が「2020」でも「r2」でも日付であると認識される。

また、D5に「分」数を入力するとそれが何時間何分になるかを表示する数式は以下のとおり。

=TIME(0,D5,0)

この場合も、60以上の数値を入力すると「時間」が正常に繰り上がる。たとえばD5が 135 であれば、2:15 になる。

特定の日付（シリアル値）をもとにして、別のシリアル値を得る場合、もとのシリアル値を年・月・日（・時・分・秒）に分解してから DATE() 、TIME() で再度シリアル値に変換する、といった操作が便利。

eg) B6 に誕生日「 1970/6/4 」がシリアル値として入力されているとき、今年の誕生日「 2020/6/4 」を求める関数は、

=DATE(YEAR(TODAY()),MONTH(B6),DAY(B6))

Lesson 3で考察したような、「シリアル値から日付のデータを取り除く（or 時間のデータを取り除く）」という処理は、TIME() 関数、DATE() 関数を利用し、YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND() を使って以下のような数式で表現することもできる。

=TIME(HOUR(B5),MINUTE(B5),SECOND(B5))
=DATE(YEAR(B5),MONTH(B5),DAY(B5))

VBA補足：
VBA では、DATE() と同様の使い方をする DateSerial() 関数、TIME() と同様の使い方をする TimeSerial() 関数がある。

◆ シリアル値から（年・月・日・）時・分・秒へ

“7:18” のようなシリアル値で表現された特定の時間（セルB1に入っているとする）を「時」「分」「秒」にするには、

時：=B1*24               → 7.3
分：=B1*1440          → 438
秒：=B1*86400       → 26280

のように掛け算するだけ（シリアル値は 1日=1 なので。1日は24時間であり、1440分であり、86400秒である。閏秒のことはここでは考えないこととする。）

eg)
時給1200円の人が、B5に入っている時間（"4:54" などのシリアル値）
働いたときの賃金は
=B5*24*1200

3時間46分は何秒か、を求めるなら
="3:46"*86400

また、さきほどの
=TIME(0,D5,0)
は、「分」数をシリアル値に変換するのと同義だから、
=D5/1440
でも同じ結果を返す。

応用：
Lesson 2でみた「（1時間単位で）いま何時台か」を返す
=FLOOR(NOW(),"1:00")
は、
=INT(NOW()*24)/24
でも同じ結果を返す。

VBA補足：
VBA には FLOOR() 、CEILING() 、MROUND() にあたる関数が存在しない。従って、時間をまるめるには
Application.WorksheetFunction プロパティを使うか、上記のようなテクニックを使うことになる。

練習問題A：
たとえば7時間28分であれば「7.28」など、時と分のあいだを小数点で区切った数値として入力したセル C5 があるとする。
この値を通常のシリアル値（ "7:28" あるいは 0.3111111…… ）に変換する数式を作成しなさい。

解答例：
=(INT(C5)+(C5-INT(C5))*5/3)/24
別解
=TIME(INT(C5),(C5-INT(C5))*100,)

練習問題B:
22:50 のような時刻表示（シリアル値。時・分のみで秒は入力されていないとする）が入ったセルC5があるとする。これを2250のような数値に変換する関数を作成しなさい。

解答例: 
=INT(C5*24)*100+(C5*24-INT(C5*24))*60
別解
=HOUR(C5)*100+MINUTE(C5)

練習問題Ｃ
2020/5/8 11:37:24.5 のように、日付・時刻データ（シリアル値）が入ったセルC5があるとする。これから、11:37 のように、日付データを取り除いて「時+分」のみを取り出す（秒は切り全て）関数を作成しなさい。

解答例
=INT((C5-INT(C5))*1440)/1440
別解
=TIME(HOUR(C5),MINUTE(C5),)

補足メモ：
セルに入った値自体を変えずに表示形式のみを、分単位、秒単位にしたい場合は、Excel では、[h] と同様、
[m] 、[s] といった表現を用いることができる。

シリアル値を「月」「年」に直す場合は、月の日数、うるう年などを考える必要があるため、一義には決まらない。
月の日数や閏年の扱いについては引き続き Lesson 5 で考察するが、次節で紹介する DATEDIF() はこれらを調整したうえで年数、月数、日数を計算する関数である。

■ DATEDIF() --- 期間を求める

一般に、2つの日付のあいだの期間を求めるには、
DATEDIF( [日付1] , [日付2] , [条件] ) 関数を使うことができる。

=DATEDIF(B5,C5,"Y")
（B5とC5のあいだの年数が返る。端数切り捨て）

「条件」には次のようなものがある：
"Y" --- 年数
"M" --- 月数
"D" --- 日数
"YM" --- 1年に満たない部分（年で割った余り）の月数
"MD" --- 1月に満たない部分（月で割った余り）の日数
"YD" --- 1年に満たない部分（年で割った余り）の日数

eg)
いま自分が何歳かを返す
=DATEDIF("1970/6/4",TODAY(),"Y")

応用：
生年月日から今年の誕生日を求め、今年何歳になるかを返す

　　 A     B   
1 生年月日 1970/6/4
2 誕生日   =DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))
3 年齢     =DATEDIF(B1,B2,"Y")

また、今日は直近の誕生日から何日目かを求めるなら

=DATEDIF(B1,TODAY(),"YD")

誕生日から何ヶ月と何日目かを求めるなら

=DATEDIF(B1,TODAY(),"YM") &"ヶ月と"
&DATEDIF(B1,TODAY(),"MD") &"日"

補足メモ：
Excel 2013 以降では、DAYS() 関数が追加されている。
この関数は、
=DAYS(A1,A2)
で、
=DATEDIF(A1,A2,"D")
と同じ結果を返す。

また、「年数」についてはそれ以前からあるYEARFRAC() 関数でも求めることができる。
=YEARFRAC(A1,A2,1)
で、閏年も考慮したうえで、A1とA2のあいだの年数が求められる。結果には小数点以下の値が含まれるので必要に応じてINT()で囲むなどして処理。

VBA補足：
VBA で DATEDIF() と類似の操作を行う関数としてDateDiff() がある。名称に注意。