複雑な勤務時間管理表

質問

最近会社での勤務時間管理方法が変わり、月末タイムカードに自ら残業時間等を記入し提出せねばならず、エクセルで自動計算したいと思ってるんですが、計算項目も多く困っています。

出勤時間と退勤時間を入力するだけで以下の条件をすべて表示できますでしょうか？

1：当日実働時間　（休憩の1ｈを必ずマイナスしなければなりません）
2：当日残業時間　（実働7.5ｈを超えた場合、1分単位での計算が必要です。7.5時間を下回った場合も1分単位でのマイナス表示が必要です。）
3：深夜勤務時間　（22：00以降の1分単位での計算が必要です。24：00を超える場合も多く、当日深夜勤務にカウントされます。）
4：1～3までの各項目合計

自分で何度かチャレンジしてみたのですが、マイナス表示もある残業時間の計算や深夜勤務の24：00以降が発生した場合の計算等がうまくいきません。

入力項目が増えるのはまったく構いません。
どうかご教授お願いします。

回答No.5

KURUMITO · ベストアンサー

単に今までの理解の範囲でしたら、次のようになるでしょう。
初めにマイナスの時間も発生するとのことなので「Office」ボタンからを「Ｅｘｃｅｌｌのオプション」をクリックします。「詳細設定」の項目の「次のブックを計算する時」にある「1904年から計算する」にチェックを付けてＯＫします。
データは2行目からとして、出勤時間はＢ列、退勤時間はＣ列、実働時間はＤ列、残業時間はＥ列、深夜時間はＦ列、合計時間はＧ列とします。
Ｄ2セルには次の式を入力します。
=IF(COUNT(B2:C2)=2,C2-B2-"1:00","")
Ｅ2セルには次の式を入力します。
=IF(COUNT(B2:C2)=2,D2-"7:30","")
Ｆ2セルには次の式を入力します。
=IF(COUNT(B2:C2)=2,IF(C2>TIMEVALUE("22:00"),C2-"22:00",""),"")
Ｇ2セルには次の式を入力します。
=IF(COUNT(B2:C2)=2,SUM(D2:F2),"")
Ｄ2セルからＧ2セルまでを選択してから下方にオートフィルドラッグします。
なお、最後にＢ列からＧ列までを選択して右クリックし、「セルの書式設定」の「表示形式」で「ユーザー定義」として[h]:mmと入力しＯＫします。

回答No.8

tom04

No.1・7です！
何度もごめんなさい。

No.7の数式で
E2セルはあんなに難しくしなくていいみたいです。

=IF(D2="","",IF(B2<="22:00"*1,0,B2-"22:00"))
に訂正してください。

どうも何度も失礼しました。m(__)m

回答No.7

tom04

こんばんは！
No.1です。

補足を読ませてもらうと、単純なことではなかったのですね？

参考になるかどうか判りませんが、↓の画像で説明させていただきます。

ただ、気になるのは通常の出社時刻や退社時刻（定時）は考えないのですね？

そして、もし、出社時刻が23：00でも必ず休憩の1時間は引くということでの回答になります。

表のC2セルに
=IF(COUNTBLANK(A2:B2)>0,"","24:00"-A2-"1:00")

D2セルに
=IF(C2="","",C2-"7:30")

E2セルに
=IF(D2="","",IF(AND(B2>="22:00"*1,B2<="24:00"*1),B2-"22:00",IF(B2>"24:00"*1,B2-"22:00",0)))

という数式を入れ、C2～E2セルを範囲指定した後に、
E2セルのフィルハンドルで下へコピーしています。

尚、前回はセルの表示形式で[hh]:mm　と回答しましたが、
時間部分が一桁の場合「0」が表示されて目障りのようなので
[h]:mm　としてみてください。

以上、参考になれば幸いですが、
今回も的外れならごめんなさいね。m(__)m

お礼

お返事遅くなり申し訳ありません。

画像付きのわかりやすい回答ありがとうございます！
他の回答と共に是非参考にさせていただきます！

大変たすかりました～！

回答No.6

KURUMITO

解答Ｎｏ5です。
何時間何分のように表示するのでしたらＢ列からＧ列を選んで右クリックし、セルの表示形式のユーザー定義では[h]"時間"mm"分"と入力してＯＫします。

お礼

お返事遅くなり申し訳ありません。

大変参考になりました。
素早いご回答、大変助かりました！
ありがとうございました！！

回答No.4

nattocurry

残業時間の表示形式は、決まっているのでしょうか？
たとえば、-3時間30分の場合、-210分と表記するのはNGですか？

補足

ご回答ありがとうございます。

ＮＧではありませんが、日単位・月単位共に最終的な提出は
”3時間12分”26時間48分”の様に1分単位での表記が必要になるので、可能であれば時間と分は別に表示させたいと思っております。

よろしくお願いします。

回答No.3

nattocurry

出勤時刻と退勤時刻に、制限は無いのでしょうか？
たとえば、出勤時刻が19時00分、退勤時刻が24時00分、という入力は有り得るのでしょうか？
有り得るのであれば、その場合の、当日実働時間、当日残業時間、深夜勤務時間、のそれぞれは、いくつになるのが正しいのでしょうか？
有り得ないのであれば、なぜ有り得ないのか、という条件を提示してください。

補足

ご回答ありがとうございます。

出勤時間19：00　退勤時間24：00　は有り得ます。

この場合の各入力時間は
・当日実働時間　4時間00分（休憩1ｈを引いてます）
・当日残業時間　-3時間30分　（所定労働時間7ｈ30分に満たない時間数です）
・深夜勤務時間　22：00以降の2時間00分

となります。

よろしくお願いします。

回答No.2

KURUMITO

残業時間にマイナスとはどのような意味でしょう。マイナスなら残業時間は無いということではないのですか？

補足

ご回答ありがとうございます。

月の残業時間は（実働時間）－（所定労働時間）でカウントされる為、
月初めに残業が重なった場合実働7.5ｈに満たない時間で退勤し、
残業時間計よりマイナスされます。

ですので日単位でのカウントにはマイナスの残業が発生します。

説明不足で申し訳ありません。

回答No.1

tom04

こんにちは！

参考になるかどうかわかりませんが・・・

今、職場からなので（見積の最中です）
取り急ぎ24：00以降の計算方法の場合のみ
回答させていただきます。

24：00以降の入力方法を、たとえば午前2時20分であれば
26：20　のように入力することが前提になります。

まず、セルの書式設定から表示形式を
ユーザー定義　→　[hh]:mm　としておいてください。

そして、メニュー　→　ツール　→　オプション　→　計算方法タブで
「1904年から計算する」にチェックを入れます。

これで普通に足し算・引き算は問題なく出来て、マイナスも表示されるはずです。

以上、当方使用のExcel2003での回答でしたが、
お役に立てれば幸いです。m(__)m

補足

ご回答ありがとうございます。

24時以降の計算のためにユーザー定義もご指摘の通りに、
計算方法タブも1904年にチェックを入れた状態で作成しておりました。

何が問題なのかもわかってない状況なのです。

自分でも調べ続けますが、もしよければさらなるご回答よろしくお願いします。