時刻のシリアル値を理解して
TIME関数を的確に使いこなそう


　日付時刻関数は大きく2つに分類できます。「日付や時刻からシリアル値を求める関数」と、「シリアル値を入力し日付や時刻を求める関数」です。日付/時刻関数を使いこなすためのポイントは、このシリアル値の理解にあります。シリアル値とは、日付と時刻を数値で表わしたものをいい、エクセルでは日付と時刻をこのシリアル値に置き換え計算します。このシリアル値が入力されたセルに日付や時刻の表示形式を設定することにより、ワークシート上に正しく日付や時刻が表示されるのです。シリアル値は整数部と小数部に分かれており、整数部は日付を表し、小数部は時刻を表します。整数部は、1900年1月1日〜9999年12月31日の日付を1〜2958465の整数とし、小数部は、1日の0時0分0秒〜翌日の0時0分0秒までを0.0〜1.0としてあつかいます。たとえば、シリアル値「25982.5」は1900年1月1日から25982日後の「1971年2月19日の午後12時」のこと。ただし、ウィンドウズ版のエクセルとマッキントッシュ（マック）版のエクセルとでは標準の日付システムが異なるので注意。マック版は1904年1月2日をシリアル値「1」としています（通常マックで作成されたブックをウィンドウズで開くと、自動的に1904年の日付システムに変更。ウィンドウズ版でマック版と同じ日付システムを利用したい場合には、ツールメニューのオプションの[計算方法] パネルで、[1904年から計算する] にチェックを入れる）。
　今回は時刻関数に絞って見ていきましょう。時刻関数には、時刻を表す文字列をシリアル値に変換する「TIMEVALUE」、時刻に対応するシリアル値を返す「TIME」のほか、シリアル値をそれぞれ「時」 「分」 「秒」に変換する「HOUR」「MINUTE」 「SECOND」があります。たとえば一見簡単そうな「勤務時間」の計算も実際に行なってみると意外と難しいもの。では各時刻関数を的確に使い分ける方法を見ていきましょう。



今月の関数

「退社時間 - 出勤時間 - 休憩時間 」で勤務時間を求める。ここではさらに午前9時を過ぎた人は30分間、勤務時間から差し引かれるという式にする。（1）答えを表示させたいセルF7を選択、（2）[関数貼り付け] ボタンをクリック、IFを選択。（3）論理式には、出勤時間が9時を過ぎた場合の式を入力。[C7>] と入力し、（4）ボタンで [その他の関数...] を選び 「関数の貼り付け」ダイアログボックスを表示。（5）日付/時刻関数から [TIME] を選択。


　　　　　　　　　　　　　　　
午前9時以降なので、「時」に「9」、「分」に「0」、秒に「0」と入力（ここで [OK] を押さずに）、（6）数式バーのIFのところをクリックし、IF関数の数式パレットにもどる。


　　　　　　　　　　　　　　
「真の場合」に、「退社時間 - 出勤時間 - 休憩時間 - 30分」 の式を入力。休憩は、分のみなので、TIMEで分をシリアル値に返す。（4）ボタンで [TIME] を選び、「時」と「秒」のところに「0」と入力し「分」にE7を入力（[OK] を押さず）、（7）数式バーをクリック。30分を引くため、「-」と入れ、再びTIME関数を選択、「時」と「秒」に「0」、「分」に「30」と入力。


　　　　　　　　　　　　　　
数式バーのIFのところをクリックし、IF関数の数式パレットにもどる。（8）「偽の場合」には9時を過ぎなかったら「退社時間 - 出勤時間 - 休憩時間」という式を設定。D7からC7を引き、TIME関数を利用し、休憩時間を引く式をたて、[OK] ボタン。F8からF12までオートフィルで求める。勤務合計は、求めたいセルF13をクリックし、[オートSUM] ボタンで範囲を 「F7:F12」 にし [OK] ボタンをクリック（F7からF12までのセルの「表示形式」は「時刻」の [13:30:55]。F13は「ユーザー定義」の [[h]:mm:ss] と設定）。



F7のセルに入った式は
=IF(C7>TIME(9,0,0),
D7-C7-TIME(0,E7,0)-TIME(0,30,0),
D7-C7-TIME(0,E7,0))

シリアル値を「時」に変換するHOURと合計を求めるSUMを組み合わせ、時の合計を求める。（9）時の合計を求めるF17をクリックして、（10）[関数貼り付け] ボタンを押し、ダイアログの「日付/時刻」関数から [HOUR] を選択。


　　　　　　　　　　　　　　
HOURの数式パレットで、SUMをネストし、範囲にF7からF12を選択。ここで、合計の答えが24時間を超えるとその部分が日に繰り上がるため、INT関数を使い、超えた部分をプラス。日付/時刻データは整数部が「日」で小数部が「時」のデータとなる。INT関数は、小数点以下を切り捨てて、整数部を求めるので、「日」が求められ、1日は24時間なので、INT関数で求められた答えに24をかえる。




F17のセルに入力された式は
=HOUR(SUM(F7:F12))+INT(SUM(F7:F12))*24

「分」の合計を求める場合は、MINUTE関数にSUM関数をネストし求める。合計を出したいセルG17をクリックし、「関数の貼り付け」ダイアログの日付/時刻関数からMINUTE関数を選択、SUM関数をネストしてF7からF12までを範囲選択し [OK] ボタンをクリック。
　　　　　
G17のセルに入力された式は
=MINUTE(SUM(F7:F12))

「秒」の合計を求める場合は、SECOND関数にSUM関数をネストし求める。合計を求めたいセルH17をクリックして「関数の貼り付け」ダイアログの日付/時刻関数からSECOND関数を選択し、SUM関数をネストしてF7からF12を範囲選択し [OK] ボタンをクリック。
　　　　　
H17のセルに入力された式は
=SECOND(SUM(F7:F12))