Office 2019 Excel 2019

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

投稿日:2022年2月16日 更新日:

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

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

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

[セルの書式設定]ダイアログボックスのユーザー定義

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

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

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

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

セルの書式が[標準]

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

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

[数値の書式]が[ユーザー定義]

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

[ホーム]タブの[数値]グループの[表示形式]

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

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[h:mm]

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

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

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

[4:00]と表示されたセル

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

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

[標準書式]のセルに[28:00]と入力

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

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

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

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[[h]:mm:ss]

現在時刻の簡単入力

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

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

Excelで今の時刻を入力するには、キーボードから[Ctrl]+[:](コロン)を押すと入力できます。

キーボード[Ctrl]+[:]

詳細は以下の記事で解説しています。[Ctrl]+[;](セミコロン)で今日の日付になります。

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

キーボードから【今日の日付】と【現在の時刻】を簡単入力

Excelで現在の日付と時刻をキーボードから簡単に入力してみましょう。 まず、日付や時刻を入力したいセルをクリックしてアクティブにします。 ...

続きを見る

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

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

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

[いま]と入力した時の変換候補

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

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[h"時"mm"分"]

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

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

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

変更補一覧から時刻を選択

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

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

【きょう】と入力して今日の日付に変換【いま】と入力して現在時刻に変換

Windows10のIMEでは、日付や現在の時刻の入力が簡単にできるようになっています。 いつのアップデートで機能が追加されたのかは分かりま ...

続きを見る

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

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

出退勤管理表

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

セルの書式設定]ダイアログボックスの[ユーザー定義]-[h:mm]

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

=C5-B5-D5

勤務時間の計算式

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

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

[セルの書式設定]ダイアログボックスの[時刻]

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

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

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

=C5-B5-TIME(1,0,0)

TIME関数を使った勤務時間の数式

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

=C5-B5-TIME(0,45,0)

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

TIME 関数(Microsoft)

勤務時間の合計

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

=SUM(E5:E11)

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

勤務時間の合計

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

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

[h]:mm

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

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

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

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

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[[h]:mm]

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

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

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

[h]"時間"m"分"

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[[h]"時間"m"分"]

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

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

Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について(Microsoft)

yyyy/m/d(aaa)

[セルの書式設定]ダイアログボックスの[ユーザー定義]-[yyyy/m/d(aaa)]

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

キーボード[Ctrl]+[1]

[0:30]を[0.5]と表示するには

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

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

=E5*24

そして、書式を[標準]にします。

数式を入力して標準書式に変更したセル

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

時間の合計

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

=A1*24*60

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

分単位に表示すときの数式

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

=A1*24*60*60

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

[秒単位]で表示したい場合の数式

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

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

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

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

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

ドロップダウンリストから選択して自動的にセルに塗りつぶしを設定

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにす ...

続きを見る

年と月を選択するだけで繰り返し使用できる予定表を作成(関数使用)

Excelで予定表(スケジュール表)を作成することは多いと思いますが、毎月、作成するのは面倒ですね。 ここでは、[年]と[月]を選択するだけ ...

続きを見る

検索

ほかの記事も検索してみませんか?

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2022 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2022 AllRights Reserved Powered by AFFINGER4.