残業時間を計算したい

質問

約２年間分の残業時間を計算したいのですが、会社の出退勤システムから出力したＣＳＶファイルから上手く計算する関数などアドバイスください。

表は、下記のような形になっています。

------------
Ｃ列（日付）
2012/10/1

Ｄ列（出勤時刻）
10：27

Ｅ列（退勤時刻）
19：58
------------

これだけなら、Ｅ列からＤ列を引けば、会社にいた時間が出てくるので
そこから8時間+1時間（昼休憩）引けば残業時間が求められるのですが、日をまたいで働くなどすると

-----------
Ｄ列（出勤時刻）
10：25

Ｅ列（退勤時刻）
03：58
-----------

なんて行も存在するため、計算がめちゃめちゃになってしまいます。
日付をまたいだ行も含めて、その日、出勤～退勤まで何時間会社にいたのかを計算するためにはどんな計算式を入れれば良いでしょうか？

回答No.1

nishi6 · ベストアンサー

勤務時間の計算については会社ごとの規定があったりして簡単ではないと思いますが質問の範囲内で解答します。

添付図のように、D列「出勤時刻」、E列「退勤時刻」の場合、いずれも「時：分」で入力するとすれば、勤務時間は、

　セルE2: =E2-D2+(D2>E2)

で計算できます。

前提は24時間超の勤務はないということで、「出勤時刻」＞「退勤時刻」の場合は「+(D2>E2)」で「1」を加えています。Excelでは「1日＝24時間＝1」になります。

蛇足ですが、残業時間は、

　=IF(F2>VALUE("9:00"),F2-"9:00",0)

で計算できますが、深夜勤務や早朝勤務があると色々変わるところでしょう。

ご参考に。

お礼

ご回答ありがとうございます。
皆様のアドバイスのおかげでなんとかなりそうです。

24時間またいでいる部分もありますが、多くは無いので手入力で対応できました！

皆様にＢＡとしたいですが、最初にご回答いただいた方とさせていただきます。

回答No.3

keithin

=MOD(E1-D1,1)
と記入して、時刻の書式設定を付けておきます。

回答No.2

bunjii

>会社の出退勤システムから出力したＣＳＶファイルから上手く計算する関数などアドバイスください。
24時間を超えて会社にいるケースはありませんか？
日付は出勤時刻と退勤時刻に組み合わせで付いていませんか？
または24時を跨いで勤務したときの記録内容は日付を基準として出勤時刻があり退勤が無く、翌日は出勤が無く退勤のみと言うデータになっていませんか？

>日付をまたいだ行も含めて、その日、出勤～退勤まで何時間会社にいたのかを計算するためにはどんな計算式を入れれば良いでしょうか？
会社にいた時間が24時間未満の場合は回答No.1の数式で良いと思います。
会社にいた時間が24時間以上の場合は出勤時刻と退勤時刻に日付の情報が付加されていないと計算できません。
CSVデータの精査が先決です。