Excelで勤務時間の集計をしたいのですが、ウマくいきません。ヘルプお願いします。

質問

A列⇒始業時間、B列⇒終業時間、C列⇒休憩開始時間、D列⇒休憩終了時間を入力し、E列に定時（通常）時間数、F列に定時（深夜）時間数、G列に残業（通常）時間数、H列に残業（深夜）時間数を出すような形にしたいのですが、どうもウマくいきません。例えば、A⇒15：00、B⇒26：00、C⇒21：30、D⇒22：30と入力した時にE⇒6.5、F⇒1.5、G⇒スペース、H⇒2.0となるためにはE～Hまでどの様な式を入れたらよいのでしょうか？

回答No.4

wafumofu

#2です。
つなげてみました（笑
とても美しくない式です（爆

簡潔な式に出来る方いたらお願いしますorz

E1
=IF(AND((C1)*24<22,(D1)*24<=22),IF(22-(A1)*24-(D1-C1)*24>8,8,22-(A1)*24-(D1-C1)*24),IF(AND((C1)*24>22,(D1)*24>=22),IF(22-(A4)*24>8,8,22-(A4)*24),IF(22-(A1)*24-22+(C1)*24>8,8,22-(A1)*24-22+(C1)*24)))

F1
=IF((B1-A1-D1+C1)*24>8,8-E1,(B1-A1-D1+C1)*24-E1)

G1
=IF(AND((C1)*24<22,(D1)*24<=22),IF(22-(A1)*24-(D1-C1)*24<8,0,22-(A1)*24-(D1-C1)*24-8),IF(AND((C1)*24>22,(D1)*24>=22),IF(22-(A1)*24<8,0,22-(A1)*24-8),IF(22-(A1)*24-22+(C1)*24<8,0,22-(A1)*24-22+(C1)*24-8)))

H1
=(B1-A1-D1+C1)*24-E1-F1-G1

先ほど書き忘れましたが、G1はH1の計算式の都合上、スペースではなく0になっています。
空欄にするならツール→オプションの表示タブ
ここのゼロ値のチェックを外してください。

IF式で定時時間数が22時より前に8時間ある場合と無い場合
また、休憩時間の判定をしています。

これをすっきりさせる方法が思いつかないので、式が無駄に長くなってしまいます。

#3の方
どの辺がNGになってますか？
一応計算結果は合ってると思いますが、式の意味が違う可能性は否定出来ません。

回答No.3

p-p

ちょっと未完成です
休憩時間が　22時をまたがるか　どうかの定義の自動計算は難しいです！！

#2さんの場合　休憩が22時をはさむ場合が　NGですね
私も　はさんだ場合の場合ワケが　ちょっときついです＞＜

>列⇒始業時間、B列⇒終業時間、C列⇒休憩開始時間、D列⇒休憩終了時間を入力し、E列に定時（通常）時間数、F列に定時（深夜）時間数、G列に残業（通常）時間数、H列に残業（深夜）時間数

●　上記条件として

まず　ctrl　＋　A　キーを押し全選択し

右クリック＞セルの書式設定

A列　１５：００
B列　２６：００
C列　２１：３０
D列　２２：３０
E列　=IF(J<=1/3,J,1/3)　結果は０：００
F列　=IF(J<=1/3,J,1/3)　結果は２：００
G列　=IF(K<=1/12,1/12,K-1/12)　結果は２：００
H列　=IF(B>1,B-1,0)　結果は２：００

J列　=IF(B<=1,B-A-(D-C),1-A-(D-C))
K列　=IF(B<=11/12,0,B-11/12)

●J列とK列は　便宜上　作ったので印刷時には　範囲指定からはずしてください

●この表では　数値　１が２４：００扱いの為　22時は　２２／２４　8時間は　１／３になります

回答No.2

wafumofu

暫定版（汗
A～D列の書式設定はユーザー定義で[h]:mmにしてください。（26:00があるため）
E～H列の書式設定は標準にしてください。
なぜ暫定版かというと、休憩時間がどこになるかでEとGの式が変わってしまいます。

休憩時間が22時をまたぐ場合
E1
=IF(22-(A1)*24-22+(C1)*24>8,8,22-(A1)*24-22+(C1)*24)
F1
=IF((B1-A1-D1+C1)*24>8,8-E1,(B1-A1-D1+C1)*24-E1)
G1
=IF(22-(A1)*24-22+(C1)*24<8,0,22-(A1)*24-22+(C1)*24-8)
H1
=(B1-A1-D1+C1)*24-E1-F1-G1

休憩時間が22時より前の場合
E1
=IF(22-(A1)*24-(D1-C1)*24>8,8,22-(A1)*24-(D1-C1)*24)
F1
=IF((B1-A1-D1+C1)*24>8,8-E1,(B1-A1-D1+C1)*24-E1)
G1
=IF(22-(A1)*24-(D1-C1)*24<8,0,22-(A1)*24-(D1-C1)*24-8)
H1
=(B1-A1-D1+C1)*24-E1-F1-G1

休憩時間が22時より後の場合
E1
=IF(22-(A1)*24>8,8,22-(A1)*24)
F1
=IF((B1-A1-D1+C1)*24>8,8-E1,(B1-A1-D1+C1)*24-E1)
G1
=IF(22-(A1)*24<8,0,22-(A1)*24-8)
H1
=(B1-A1-D1+C1)*24-E1-F1-G1

もっと詳しい人が修正してくれることに期待。
IFで条件を繋げるとすごい式になりそう・・・。

お礼

何とお礼を言ったら良いのか・・・。ありがとうございます。

回答No.1

ayataichi

下記サイトが参考になるかと思います。

参考URL：

http://www.excel.studio-kazu.jp/lib/e2h/e2h.html

お礼

ありがとうございました。参考にさせていただきますね。