Excelの時間計算がうまくいかない。

質問

Excel2010です。

時間計算なのですが、出勤9：00　→　退社17：00の場合、
「退社　-　出勤」時間を引けば、8：00と表示されます。

しかし、出勤23：00　→　退社5：00の場合、
「退社　-　出勤」を引くとエラーになり「＃」が表示されます。

遅い時間から引かないとダメなのかもしれませんが、逆に「出勤　-　退社」を引いてしまうと、勤務時間が合いません。

このような場合、どういう計算をさせればいいのでしょうか？

回答No.5

bunjii · ベストアンサー

>このような場合、どういう計算をさせればいいのでしょうか？
Excelで日時を対象に計算するときの基礎知識を習得してください。
A列に日付、B列に出勤時刻、C列に退社時刻を入力するものとすれば2014/1/8 9:00に出勤し、同日の17:00に退社したときはA2セルへ2014/1/8、B2セルへ9:00、C2セルへ17:00と入力することにします。
2014/1/9 23:00に出勤し、翌日の5:00に退社したときはA3セルへ2014/1/9、B3セルへ23:00とします。
退社時刻は翌日ですからA4セルへ2014/1/10、C4セルへ5:00と入力すると論理を組み立て易くなります。

=IF(C2="","24:00"-MAX(0,B2),C2-MAX(0,B2))

この方法であれば24時間を超える連続勤務も計算できます。

Excelでは時刻を0～1のシリアル値として計算用の値としています。
日付は1900/1/1を1として経過日数を整数で表します。
時間の正しい計算は終了日時-開始日時とします。
但し、同じ日の場合は終了時刻-開始時刻でも成り立ちます。
従って、23:00に出勤して翌日5:00に退社する場合は日付の要素を加味しないと正しい計算ができません。

添付画像はExcel 2013で作成しましたがExcel 2010でも同じ方法で計算できます。

お礼

表の添付ありがとうございました。

とっても分かりやすい説明、感謝します。
単純に、時間計算を考えていたのですが、退社は翌日だとすると、添付のように日付があればいいですね。

24時間を超える勤務は…実際には有って欲しくないですが（笑）、勤務時間に限らずでもいろいろ使えそうです。

ありがとうございました。

回答No.4

RandenSai

IF関数を使うのがわかりやすいと思います。出勤時間がセルA1・退社時間がセルB1に入っている場合の式は、

=IF(A1>B1,B1+24-A1,B1-A1)

になります。要するに、出勤時間の方が大きかったら退社時間は翌日であるとして、24を足してから出勤時間を引くわけです。これだと出勤時間が8:00:00で退社時間が17:00:00だと、答えは9:00:00となり、出勤時間が17:00:00で退社時間が8:00:00なら、答えは15:00:00となります。この計算には休憩時間が入っていないため、それは適宜付け加えてください。

あと一つ注意があって、この式を入れているセルの書式設定は必ず「時刻」にしてください。自動だと時刻にならず、「標準」にされてしまって意味不明の数字が出てくることがあります。うまく自動で時刻になってくれた場合はそのままでいいですが。

お礼

休憩時間の引き方は存じていますが、逆算？が分からなかったので…。

大変よくわかりました。
ありがとうございました。

回答No.3

keithin

Ａ１に出社時刻（9:00や23:00)
Ｂ１に退社時刻（17:30や5:00）
Ｃ１に
=MOD(B1-A1,1)
として時刻形式を取り付けておけば，計算できます。

言わずもがなですがこのようなやり方では，「２４時間以上の連続勤務」は計算できません。

お礼

ありがとうございます。
色々な方法をご紹介いただいたので、試してみます。

回答No.2

noname#235638

=IF(COUNT(A1:B1)<2,"",B1+(B1<A1)-A1)

A1に23:00出社、B1に5:00退社を入力した場合は、
このように計算させる。
表示を時刻にすると、6:00:00と返してくる。

前日21時から翌日5時まで働き、
1時間の休憩をとったなら

=IF(COUNT(A1:B1)<2,"",B1+(B1<A1)-A1-"1:00")

と計算させる。
最後の　-"1:00"　は休憩時間です。

お礼

ありがとうございました。

COUNTも、こういう使い道があるのですね。
とってもよくわかりました。

回答No.1

麻野 なぎ（@AsanoNagi）

数字の見た目だけで良ければ、

「退社 - 出社 + 1」 の計算をして、結果を「時刻」で表示すれば良いです。

Excel で時刻を入力した場合、必ず、日付の情報を持ちます。
なので、本来、出勤 23:00 退社 5:00 の場合、二つの時刻は「日付が違う」はずですから、日付の情報も持たせる必要があります。

だから、「日付も一緒に入力する」のが正しい方法です。

上記に示したもの（+ 1）するのは、
出勤23：00　→　退社5：00の場合 は、「翌日の 5:00 から、前日の 23:00 を引く」のと同じ結果になります。
出勤9：00　→　退社17：00の場合 は、「同日の 17:00 から 9:00 を引いた結果に１日加算」下のと同じ結果になります。

後者は１日結果が異なりますが、「時刻として表示」するだけなら、「異なる１日」は見えないので、正常に計算できているように見えます。
ただし、計算結果そのものは、正しくないので、この時間をさらに集計に使う場合は、int() などを併用して、小数部分だけを取り出しておく必要があります。

お礼

色々な計算方法があるのでっすね。
int関数は使ったことがあるので、そうそう難しい計算ではありませんね。

ありがとうございました。