Office Hack

11時から3時を引く計算をします。B2セルに『11:00』、C2セルに『3:00』と入力します。すると、赤枠の表示形式の欄が「標準→ユーザー定義」に自動的に変更されます。

セルの書式設定を見てみると、デフォルトでユーザー定義の分類の中に「h:mm」にカーソルが当たっております。hは時間、mmは分を表しており、「11:00」はただの数字ではなく、時間として認識をしていますよという意味となります。

B2からC2を引き算します。

時間同士の計算と認識されておりますので、正しい「8:00」が算出されました。

ここでは年/月/日と時:分の引き算方法を行います。すべて半角で入力してください。年/月/日と時:分の間は、半角スペースで空けます。

B2からC2を引き算します。逆でも可。

結果を見るとシリアル値で表示されました。右上の赤枠を見ると表示形式が「標準」となっているためです。

表示形式を変更していきます。【プルダウンメニューをクリック】していただき、一番下の【その他の表示形式】を選択します。

分類の【ユーザー定義】を選択し、【h:mm】を選択し、【OK】ボタンを押します。

正しい時間表記となりました。#######と表示されてしまう方は、下記の「時間のマイナス表示について」のコンテンツをご確認ください。

11時から12時を引いてみます。本来なら「-1:00」と表示されるところ、#######と表示されてしまいました。

これはExcelが裏側で、時間の数値を「シリアル値」という連続した値で持っており、通常の時間の引き算はこのシリアル値同士の差分で計算しております。しかしシリアル値はマイナスの時間を計算することができないので、#######と表示されてしまいます。

これを解消するためにはあるチェックボックスをオンにするだけで解消できます。次からその手順をご説明します。

【ファイル】タブを選択します。

【オプション】を選択します。

「Excelのオプション」ダイアログボックスが開かれますので、【詳細設定】を選択します。

一番下までスクロールして、【1904年から計算するをチェック】にチェックを入れて【OK】ボタンを押します。

1900年1月1日が初期値として定められておりますが、チェックすると1904年1月1日を初期値として変更できます。これはMacでそのような仕様となっており、Mac版のエクセルの互換性を保つために用意されております。

時計のマイナス表示が可能となりました。

初期値を変更した際に気をつけなければならないのは、別のセルで日付の計算をしているときに4年と1日だけ、ズレが生じてしまいます。その回避策としては、以下の関数を利用すると便利かもしれません。

TEXT関数の書式は、=TEXT(数値,表示形式)となります。第1引数に「時間の引き算の結果」、第2引数に「時間の表示形式"h:mm"」を指定します。

もともとはシリアル値で表示されているため、時間の表示形式に変更する必要があります。

#VALUE!エラーが出てしまいました。これはまだマイナスの値のまま表示形式を変換しただけですので、エラーとなります。そこでABS関数を利用して、マイナスの値を絶対値に変換します。

ABS関数の書式は、=ABS(数値)となります。数値を絶対値に変換してくれます。

絶対値とは、「+（プラス）や-（マイナス）のつかない数字」のことを言います。0からどれだけ離れているかという意味で認識いただくと分かりやすいかもしれません。

絶対値なので負の値が正の値に置き換わりました。正しい表記は-1:00にしないとならないので、次のIF関数で条件分岐を指定していきます。

IF関数の書式は、=IF(論理式,[真の場合],[偽の場合])となります。

論理式は「B2セルがC2セルより大きい場合」で、それが真の場合マイナスを付けない、偽の場合マイナスを付けるという式を組みました。

結果としては、「B2セル(11:00)がC2セル(12:00)より小さい」ので偽となり、マイナスが付与されます。

真の値も試してみましょう。B2セルを13:00に変更すればB2>C2が成り立ちますので、赤枠の部分はマイナスが外れます。

これらの関数を組み合わせれば、【1904年から計算するをチェック】をオンにしなくとも、時間のマイナス表示が可能となります。

21時に出勤して1時間の休憩を取り、翌朝6時に退勤した場合、通常であれば「退勤時間-出勤時間-休憩時間」で成り立ちます。

その結果は上記で示したとおり、マイナスのシリアル値となるため######で表記されてしまいます。

これを回避するために（マイナスにならないように）、退勤時間に『+1』を加えます。これを行うことにより24時間分足されたと認識してください。

正しい結果「8:00」が表示されました。

他のセルへ反映します。