はじめに
Excelなどの表計算ソフトを使って時間を入力する際、25:00のように24時以降の時間を入力すると「1:00」や「25:00:00」と他と異なる表示をされてヤキモキすることがありますよね。…よね？
少なくとも私は、そんなもやもやを感じるので解消する方法を書きました。
この記事で取り扱う表計算ソフト
- Excel（Microsoft 365）
- Google スプレッドシート
Google スプレッドシートは以降「スプレッドシート」と表記します。
事象の説明から
表記ブレの例
「はじめに」で述べたように、表記がブレる例が以下のExcelの画像となります。（スプレッドシートも同様の表示になります）
①の表示になる流れ
上記画像内の１は、事前に23:59以前の時刻（例：18:00）を入力していたセルに、「26:00」を入力した例です。
0:00～23:59までの時刻を入力すると、書式「h:mm」がセルに設定されます。その後、データ変更で24:00以降の時刻を入力すると、以前の書式が適用され「2:00」と表示されます。
②の表示になる流れ
上記画像内の２は、何も入力していなかったセルに「27:00」を入力した例です。この場合、Excelなどの表計算ソフトは賢く判断してくれるのですが、残念なことに「27:00:00」と秒が含まれる表示になってしまいます。
解決方法
Excel、スプレッドシートどちらも、セルの書式設定を変更することで解決できます。
それぞれ、変更の流れを以下に記載していきます。
Excel
セルの書式設定を変更する流れは以下の通りです。
- 24:00以降の時間を表示したいセルを選択する。
- 「Ctrl + 1」または「右クリック→セルの書式設定」で、セルの書式設定画面を開く
- 「表示形式」タブの分類「ユーザー定義」を選択
- 「種類」のテキストボックスに、[h]:mm を入力し、「OK」ボタンを押す。
2～3 セルの書式設定画面を開いて、ユーザー定義を選択
4 書式を変更（[h]:mmを指定）
4′ 「OK」ボタンをクリックした後の時間表示
C列の書式はh:mm形式ですが、24:00以降の時刻も入力した時刻のままとなりました。
スプレッドシート
セルの書式設定を変更する流れは以下の通りです。
- 24:00以降の時間を表示したいセルを選択する。
- メニューの「表示形式」から「数字」を選び、さらに「カスタム数値形式」を選択する。
- 一番上のテキストボックスに、[h]:mm を入力し、「適用」ボタンを押す。
1 変更前の時刻入力状況
2 メニュー選択の流れ
2～3 書式設定画面の表示（変更前）
3 書式変更後の画面（適用ボタンクリック前）
3′ 適用ボタンクリック後の時刻表示
こちらも、Excel同様に24:00以降の時刻表記を整えることができました。
合計表示も同じ設定で。
ここまでの事例は、「27:00」など24時を超えた場合の入力に対してでした。が、項目タイトルの通り時刻を合計した場合にも効果を見せます。
Excelもスプレッドシートも「月間合計」行があったのですが、そこにE列の合計を計算した結果を表示するようにしました。（一部の行を非表示にしているため、加算している時間数が隠れてしまっています）
Excel
ご覧の通り、「218:00」が表示されます。なお、書式設定をh:mmにすると「2:00」になります。（218 ÷ 24 = 9あまり2、あまりの2が時間数で表示されます）
スプレッドシート
こちらも合計値が24を超えた値「136:00」で表示されます。こちらもExcelのように書式をh:mmにすると「16:00」になります。（計算式は省略）
最後に
結論としては、どちらもセルの書式を [h]:mm にしてあげれば解決する問題でしたね。ただ、書式設定の画面を開いたりするので、Excel慣れしていないと解決方法がパッと浮かばないかもしれません。
なお、ご覧の通り時間表示で使うテクニックなので、あまり日の目を浴びることはなくていいかもしれませんね。良い労働ライフを！
コメント