No.049

負の時刻を入力し、計算に使用するには（1904年オプション使用）

2003/2007/2010/2013

普通時刻は「6:00」といった表示ですが、その正体はプラスの小数値で、1日の最初 0:00 が 0、24時間（1日）後の 0:00 を 1 として、小数値で表されます。
したがって、お昼の 12:00 は1日の中間地点ですから、0.5 という値になります。
こういった、時刻や日付の値をシリアル値といい、したがって、時間を計算し、合計時間などを出すことができるようになっています。

さて今回は、負の時刻を扱いたいというお問い合わせです。

既定設定のExcelで負の時刻、例えば「-6:00」と入力しようとすると、時刻は正の小数値と定義されており、マイナスから入力するため、Excelは数式のミスであると検知さし、「入力した数式は正しくありません」と言ってきます。
負の時刻を見せるだけなら「'-6:00」と最初にクォーテーションを入れて入力し、右揃えにするのが一番早いでしょうか。
または、「6:00」と入力し、表示形式を「-h:mm」と設定してもいいかもしれません。

問題は、負の時刻で計算したいとか、計算すると負の時刻が発生するようなケースです。

解決法の1つとして、オプション設定により、負の時刻を計算に使用することができるようになります。
早速その設定場所をご紹介し、そのあとで表示形式、注意点などを補足していきます。設定は次のとおりです。

［ファイル］タブの［オプション］をクリックし、［Excel のオプション］を表示します。
［Excel のオプション］では、左側で［詳細設定］を選択し、［次のブックを計算するとき:（ブック名）］の［1904年から計算する］チェック ボックスをオンに設定します。

オプション設定後、負の時刻は「-"6:00"」などと、ダブルクォーテーションで囲んで入力します。
しかし「-0.25」と負の数値で表示されます（この値は時刻のシリアル値）ので、表示形式を時刻にします。
［ホーム］タブの［数値］グループの［表示形式］ボックスの▼より［時刻］を選択すると、「-6:00:00」と表示されます。
秒がいらない場合は、［表示形式］ボックスから［その他の表示形式］もしくは、セルでショートカット キー「Ctrl」＋「1」を押して［セルの書式設定］ダイアログ ボックスを表示します。
ダイアログ ボックス［表示形式］タブの［分類］を［時刻］にして、右の［種類］に表示されている「13:30」を選択して［OK］をクリックします。

設定後は［OK］ボタンをクリックして閉じます。
［Excel のオプション］の［1904年から計算する］の設定により、「-"6:00"」と入力し、時刻の表示形式に設定することもでき、時刻の計算にも使用することができます。
ひとまずこれで、目的は果たせました。

このあとの「ヘルプmemo」で、この［1904年～］の設定の説明と重要な注意点を書いていきますので、必ずお読みください。

また、このオプション設定を使わずに、関数と条件付き書式の表示形式で負の時刻の計算に挑戦する方法も後日ご案内する予定です。（多少難しく面倒）
ヘルプ「負の時刻を入力し、計算に使用するには（1904年オプション使用しない）」をご参照ください。

ヘルプmemo

＜［1904年から計算する］設定について＞

ではこの、［1904年から計算する］という設定の意味と注意点、設定ができた経緯をご案内します。

WindowsのExcelの日付データは、1900年1月1日を起点として1、2、3・・・と連番がふられており、シリアル値と呼びます。
一方Mac版のExcelは、1904年1月2日を起点として1、2、3・・・です。
Windows版で、日付データの表示形式を［標準］に設定すると、その日が1900年1月1日から何日目であるかのシリアル値が表示されます。

さて、先のオプション設定で、［1904年から計算する］チェック ボックスをオンに設定するということは、日付の起点を Mac版に変えるということです

下図は、1 が入力されているセルの、［1904年から計算する］がオフであるWin版（左）とオンであるMac版（右）で、日付表示に変更する表示形式設定です。

重要な注意点というのは、この［1904年から計算する］の設定（ブック単位の設定）により、ブック内のシートにすでに入力されている日付データは、4年と1日ずれてしまいます。
その場合は書き直さないといけないことになります。

では、そもそもこの1900年と1904年という設定がなぜ存在するかというと、次のようなことがExcel 誕生当時にあったようです。

1. もともと Excel は Mac版 が最初で1904年起点だった
2. 当時 Lotus1-2-3 という表計算ソフトが優位だった
3. Win版Excel は、Lotus に合わせた

そのほか、1900年設定のおもしろ現象として、「1900/2/29」という日付、1900年はうるう年ではないのですが、このない日が存在したり、数値0のセルを日付表示にすると「1900/1/0」というこれまた存在しない日付にななったりと、これらは Lotus のバグです。（一度入力して確かめてみてください。）
このバグは Lotus との互換性を保つための必然的に引き継がれ、1900年と1904年の設定は、Win版とMac版との互換性を保つための必要な設定ということなのです。

最後に、Macでの1904年の設定があるのは、負の時刻を扱いたかったからではなく、うるう年の算出のためなのです。
1904年から2069年までを扱うならば、うるう年が4年に1度の法則が成り立つのだそうです。

そしてMac版 Excel2011 以降、既定が1900年となっており、互換性が保たれています。