No.013

《条件付き書式》 1904年オプション使用せずに、負の時刻を計算に使用するには

2003/2007/2010/2013

負の時刻を扱った計算をしたい、という場合に、［Excel のオプション］の設定［1904年から計算する］チェック ボックスをオンにする方法があります。
簡単にできるのですが、ただこの設定の問題点は、そのブックに日付が入力されていた場合に4年と1日ズレてしまうということです。
詳しくはヘルプ「負の時刻を入力し、計算に使用するには（1904年オプション使用）」の「ヘルプmemo」をご参照ください。

そこで今回は、この1904年オプションの設定を使用せずに、負の時刻を計算できないかという課題に取り組んだものを1例ご案内します。

まずおさらいです。
時刻のシリアル値は24時間を 0 から 1未満の小数値で表したもので、お昼の12時はど真ん中ですので、数値のシリアル値は 0.5 、朝6時は 0.25、夕方6時は 0.75 となります。

そして24時は 1 となりますが、これは1日、整数値は日のシリアル値になります。
24時以上の時刻のシリアル値はまた 0 に戻り小数値となります。
例えば、30:00 と入力したとき、そのまま表示されますが、表示形式を時刻に設定すると、6:00 になります。

こういった時刻表示の特徴を借りて、正の時刻は通常どおり、負の時刻の場合は 100日（2400時間）をプラスした時刻を入力することに決めて、条件付き書式で表示形式を負の時刻に設定します。
例えば、「-6:00」と表示したい場合、2400時間を足した「2406:00」と入力します。

既定では「2406:00」と入力した場合、表示形式を［時刻］にすると「6:00」、表示形式を［標準］にすると「100.25」と表示されますが、条件付き書式によって、100（2400時間）以上の値が入力された場合、表示形式を（強制的に）負の時刻にするようにします。

では、その条件付き書式の設定です。

時刻を入力するセル（正負どちらも）を選択し、［ホーム］タブ－［スタイル］グループ－［条件付き書式］▼－［ルールの管理］をクリックし、［条件付き書式ルールの管理］ダイアログ ボックスを開きます。

［新規ルール］ボタンをクリックし、［新しい書式ルール］ダイアログ ボックスを開き、［指定の値を含むセルだけを書式設定］を指定します。
まずは正の時刻の表示形式の設定で、次のように指定します。
　　［セルの値］［次の値より小さい］「100」
続けて［書式］ボタンをクリックし、［セルの書式設定］ダイアログ ボックスの［表示形式］タブで、［分類］を［ユーザー定義］に指定し、［種類］ボックスに「h:mm」と指定します。

正の時刻のルールが作成できました。続けて負の時刻の設定です。
［条件付き書式ルールの管理］ダイアログ ボックスで［新規ルール］ボタンをクリックし、同様に次のような条件および書式を設定します。
次のセルのみを書式設定:
　　［セルの値］［次の値以上］「100」
［書式］ボタンより先ほどと同様に［表示形式］タブで［ユーザー定義］を指定し、［種類］ボックスに「-h:mm」と指定します。

以上の条件付き書式の設定で、正の時刻は通常どおりの入力、負の時刻で「-6:00」としたい場合は、「2406:00」と100日（2400時間）をプラスした時刻で入力します。

上図のセルD列には、時刻を数値化したものです。（使用した式はE列）
時刻の数値化は、8:30 の場合 8.5 といった数値データで表す方法です。
今回、セルの見た目ではわかりづらいので、参考にしてください。

ところでなぜ、100日（2400時間）としたかというと、24（時間）の倍数であることと入力がわかりやすいといった理由です。
さてここまでが、負の時刻入力のための説明です。

今度は、時刻の計算をしたいと思います。
B列に時刻が入力されていて、時刻の合計値を求めたいと思います。
もちろん入力値には負の時刻を表す「2406:00」などと入力した値も含まれ、答えは合計時刻（24時間を超えた時刻）になる可能性もあるとします。
（ただし、入力する時刻は ±24:00 以内、合計時刻は ±100時間を超えないとする）

では、1つずついきましょう。

正の時刻の合計は、100未満の値の合計で、次の式となります。（セルB8）

　　=SUMIF(B2:B6,"<100",B2:B6)

負の時刻の合計は、100以上の値の合計です、次の式となります。（セルB9）

　　=SUMIF(B3:B6,">=100",B3:B6)

しかし、負の時刻は見た目はよいのですが、実際毎度100日（2400時間）が個数分加算されています。
セルD9の数値化された値をみていただければと思います。

とすると、2400時間、つまり100（日）の倍数を引いた半端な値が、実際の負の時刻の合計ということです。
これは、負の合計値（セルB9）を100で割った余りが相当しますので、次の式で求めることができます。（セルB10）

　　=MOD(B9,100)

さて正の時刻、負の時刻の合計が求めることができました。
セルB10の負の時刻の合計は2400時間（100日）を足しているものではないので、条件付き書式が設定されていても、マイナスは表示されません。

これらの時間の負の時刻の合計を求めてみましょう。安易に足し算はできません。
IF関数を使用して、正の時刻合計が負の時刻合計より大きい場合、「正時刻-負時刻」の通常引き算、負の方が大きい場合は「負時刻-正時刻」の引き算の値に2400をプラスし、さきほどの条件付き書式にによって負の時刻の値にします。（セルB12）

　　=IF(B8>B10,B8-B10,B10-B8+2400)

合計値が24時を超えない場合は、これでいけるかと思います。

合計値が24時を超える場合は、正の時刻および負の時刻の条件付き書式の書式の設定を、それぞれ「[h]:mm」（正）、「-[h]:mm」（負）と設定します。

少しややこしいではありますが、こういった方法で負の時刻を扱い計算する、また別のシートやセルで普通の日付を1904年スタート換算せずに通常どおりの入力ができるようになります。

なお、時間の数値化については ヘルプ「時刻の「8:30」を「8.5」と数値に変換する」を、24時以上の時刻の表示については ヘルプ「《表示形式》 24時間を越えた経過時間を表示するには（時刻の合計について）」に詳しくありますのでご参照ください。