「知っていると得するかもしれないExcelの癖」シリーズ第十四回、テーマは「時刻の計算」になります。
Excel 2016バージョン1902を使用して確認しています。
時刻だけを取り出して計算することはあまり無い事と存じます。
一般的には年月日と時刻がセットで取り扱うことが多いと思いますが、時刻だけ取り出して計算する際のあれこれを備忘録を兼ねてまとめておきます。
TIME関数でセットされる年月日
TIME関数の前に、少しだけWEEKDAY関数について触れさせてください。
なぜWEEKDAY関数を先にしたかというと、空白セルに対してWEEKDAY関数で値を調べると土曜日が返ってくるというのはネット上に書かれているからです。
土曜日が返ってくる理由としてはExcelは「1900年1月1日」を1とした経過日数をシリアル値として使っているからであることも説明されていますが「1900年1月1日」が日曜日であるために、空白セルはその前にあたるという事で土曜日が返るようです。
シリアル値については、DAYS関数を使えば下記の等式が成り立つことで実際に値がそうなっていることを確かめることができます。
「今日の日付のシリアル値(日付を標準の表示形式した時に表示される数値)」＝DAYS("今日の日付","1900/1/1")+1
話をTIME関数に戻しますと、実はTIME関数だけで使用して求められる時刻もWEEKDAY関数で調べると土曜日が返ってきます。
同様に、YEAR関数、MONTH関数、DAY関数で年月日を求めると、年月日を指定しなければ「1900/1/0」が設定されていることが解ります。
1900/1/0を使用する上で注意しなければならない点
ただし「1900/1/0」につきましては、1点気を付けなければならない事があります。
それはVALUE関数で日付を扱う時です。
B1セルには「=VALUE(A1)」、B2セルにはこれを数式コピーしています。
表示形式はA列、B列ともに「標準」になっていますが、B2セルはエラーになります。
「数式で使用されるデータの形式が正しくありません。」というエラーのメッセージになります。
何とも残念な結果ですが、VALUE関数で日付を扱う時は、「1900/1/1」を使用する必要があります。
時刻の表示形式
時刻をセルにセットする時の表示形式は、一般的には下記を使用されていることと存じます。
[h]:mm [h]は24時間を超える時間も表現する形式になります。
従って、上記の表示形式で本日の時刻を表示させると、概算で24時間×365日×119年+うるう年30回×24時間を計算すると100万時間を超える時間が表示されることになります。
時間の計算をする場合
開始時刻と終了時刻の差で、経過時間を計算する場合ですが、何も今日の日付と時刻にこだわる必要はないように思います。
秒まで計算結果に反映しなければならない場合は、「うるう秒」があるためにうるう秒をまたぐ計算をする際は注意が必要ですが、Microsoftの下記ページを見る限りOS的には考慮されていないので、Excelでもうるう秒が反映されないように思われます。
となりますと、経過時間を計算する場合、特に日をまたぐような場合は、実際の年月日を使用するよりもあえて「1900/1/1」を使用した方が計算を楽に行うことができます。
まとめ
時間計算をする数式の中に、固定定数として「1900/1/1」を指定していると、「何か見当違いな計算をしているのではないか?」と思われがちですが、実際には計算のための負荷を減らし合理的に結果を求めるために使用しています。
本当は経過時間の計算を考えると「1900/1/0」を使用した方が計算が楽になる場合もあるのですが、「1900/1/0」にするとVALUE関数が使用できなくなるので、やむを得ず「1900/1/1」を使用することになります。
Excelの30年を超える歴史のなかで、これまではそうであったので、これから先もExcelが急に「1900/1/1」を使用しなくなることは無い事と存じます。
従いまして、数式の中に「1900/1/1」がでてきたとしても、「間違ったことをしているのでは?」と思わないでいただけば幸いです。
以上、最後までご一読いただきありがとうございました。
