勤務時間の計算と時間と時刻の表示

Excelで時間の計算を行うときは、表示形式を変更して正しい表示になるように気をつけなければならない場合があります。

勤務管理表などを作成して勤務時間を合計したら、正しい合計時間が表示されないということがあります。

これは、24時を過ぎると0時にリセットされる時刻表示になっているのが原因です。セルの表示形式を変更すれば解決できます。

時刻を入力したときの書式

セルに時刻を表示したい場合は、数字と［:］（コロン）を使って、［8:00］や［12:30］のように入力します。

そうすると、自動的に時刻と認識されて表示形式が［標準］から［ユーザー定義］に変わります。

セル［A1］は、［ホーム］タブの［数値］グループの［数値の書式］は［標準］になっています。

セル［A1］に時刻を入力します。半角で［8:00］と入力すると、［数値の書式］は［ユーザー定義］に変わります。

自動的に、セルの書式が変わったということです。

［ホーム］タブの［数値］グループの［表示形式］をクリックして確認してみます。

［セルの書式設定］ダイアログボックスが表示され、［ユーザー定義］で［h:mm］が選択されています。

24時より大きい数値を入力した場合

セル［A1］に［28:00］と入力すると、以下のように［4:00］と表示されてしまいます。

これは、先に［8:00］と入力して書式設定がされた後に、入力したためです。

［標準書式］のセルに［28:00］と入力すると、以下のようになります。

これもまた、自動でユーザー定義が設定されます。

この場合のユーザー定義は以下のように［[h]:mm:ss］となっています。

このようにExcelで時刻を入力するときは、表示形式を意識しないと求めたい値を表示できないということがあります。

書式設定したつもりはないけど、知らないうちに自動で書式設定されていたということもあります。

現在時刻の簡単入力

現在時刻の入力は、直接入力してもいいですが、ショートカットキーや予測入力機能を使って入力することもできます。

ショートカットキーで入力

Excelで今の時刻を入力するには、キーボードから［Ctrl］+［:］（コロン）を押すと入力できます。

詳細は以下の記事で解説しています。［Ctrl］+［;］（セミコロン）で今日の日付になります。

［Ctrl］+［;］で日付を入力した後、空白スペースを入力して、［Ctrl］+［:］で、1つのセルに日付と時刻を入力することもできます。

IMEの予測候補から選択して入力

また、IMEの機能を使って［いま］と入力すると、予測候補が表示されて時刻を入力できます。

参考予測変換候補がオンになっている場合です。

この場合の書式は、自動で［h"時"mm"分"］となります。

IMEの入力候補で［Tab］キーを押すと、他の変換候補も表示されますので、その中から選択して入力することもできます。

以下の変換候補で［14:26］を選択した場合の書式は、［h:mm］になります。

ただし、［午後2時26分］という表示を選択すると、書式は［標準］のままです。時刻にはなりませんので気をつけてください。

参考［きょう］と入力すると日付が入力できますが、この場合も時刻と同じように書式が自動設定されることがあります。

Windows 10の予測入力については、以下で解説しています。

出退勤管理表で勤務時間を計算

以下のような出退勤管理表を作成したとします。

セル［B5］からセル［E11］までのセル範囲の書式は、［h:mm］としています。

勤務時間を計算するには、［退勤時刻］-［出勤時刻］-［休憩時間］で求められますので、以下のような数式になります。

参考時刻の表示形式は［セルの書式設定］ダイアログボックスの［時刻］から変更することができます。選択すると、サンプルに表示されますので確認しながら選ぶことができます。

表示が変わるだけで、元の値が変わるわけではありません。

休憩時間が固定されている場合

もし、休憩時間は1時間と固定されているというような場合は、以下のような数式で求められます。

［退勤時刻］-［出勤時刻］-TIME(1,0,0) という式になります。

45分の休憩であれば、以下のようになります。

参考TIME関数については、以下のMicrosoftの記事を参照してください。

TIME 関数 - Microsoft サポート（Microsoft）

勤務時間の合計

勤務時間の合計をセル［F2］に表示したいと思います。以下のようにSUM関数を入力します。

セルの書式が［標準］のままでは、以下のように求めたい値にはなりません。

書式を変更しなければなりません。

［セルの書式設定］ダイアログボックスを表示して、［ユーザー定義］で以下のように修正します。

サンプルを見ると、求めたい数値になっていると思います。

［h:mm］にしても、求める値にはなりませんので注意してください。

［h:mm］は、24時を過ぎると0時にリセットされる時刻表示になります。

［[h]:mm］は、24時間を超える時間の表示になります。

［[h]:mm］と入力するには、［h:mm］を選択して［h］を角確固で囲んでもいいですし、［[h]:mm:ss］を選択して、［:ss］を削除してもいいです。

一度、ユーザー定義で作成した書式は保存され、同一ブック内であれば選択のみで使用できます。

また、以下のような書式にすることもできます。

参考日付の曜日表示は、［ユーザー定義］で以下のようにお尻に［(aaa)］を付けると、表示されます。

ユーザー定義については、以下のMicrosoftのページを参照してください。

表示形式の書式記号 - Microsoft サポート（Microsoft）

［セルの書式設定］ダイアログボックスは、［Ctrl］+［1］で表示できます。

［0:30］を［0.5］と表示するには

［0:30］を［0.5］のように表示したい場合もあると思います。

その場合は、時間に［24］をかけます。ここでは、以下のような数式になります。

そして、書式を［標準］にします。

このまま、合計を求める場合も書式は、［標準］にします。

参考［分単位］で表示したい場合は、さらに60をかけます。以下は、［1:30］を［90］と表示したい場合の例です。

この場合も書式は、［標準］にします。

［秒単位］で表示したい場合は、さらに60をかけます。以下は、［0:01］を［60］と表示したい場合の例です。

この場合も書式は、［標準］にします。

参考Microsoftの勤怠管理表のテンプレートは、シンプルで使いやすいと思います。

ユーザー定義や関数の学習にもなると思います。

在宅勤務対応出勤表 - 無料テンプレート公開中 - 楽しもう Office（Microsoft）

入力規則を使ったドロップダウンでの選択や、条件付き書式を使った塗りつぶしなどの設定は、以下の記事で解説しています。

Microsoftのテンプレートに機能を追加して編集するのも楽しいと思います。