エクセルで残業時間を時間帯によって＋１時間＋30分にしたい

質問

関数がわからなくて困っています。
基本は8:30～17:30
残業（早出含む）とされる時間帯
　5:00～7:30／17:30～19:30　この時間帯は1.0時間刻み
　（退社が18:00だと残業は0）
　7:30～8:30／19:30～24:00　この時間帯は0.5時間刻み
　（退社が20:00だと残業は2.5）
休憩は1:00（12:00～13:00）
以上の条件でＣ列―出社時刻、Ｄ列―退社時刻、
　　　　　　Ｅ列―勤務時間、Ｆ列―残業時間
で表を作った場合、Ｆ列の残業時間を出す式がわかりません。
（Ｅ列の勤務時間もですね。自分で考えたのはアヤシイ）
E/F列は、例えば1時間30分なら1.5と出てくるようにします。
7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる
ようにしたいのです。
時間の計算が苦手で・・・。どなたか教えてください。

回答No.6

takesun · ベストアンサー

No.5です。すいません、条件のはっきりしない部分もありましたので
十分な検証をしていませんでした。
(やはり、17:30より早い時間を入力するケースなどがあるのですね。)
どちらにしましても一部式もおかしかったので、F2の式は下記に訂正を
致します。
E2 =(MIN("17:30",D4)-MAX(C4,"8:30")
　　-IF(C4-"13:00"<0,"13:00"-MAX(C4,"12:00"),0))*24
一行につなげて入力してください。
1行目部分はトータル勤務時間、2行目が昼休み分の引き算です。
条件がありませんので、勤務時間は1分単位で計算しています。

F2 =(IF("7:30"-C3>0,FLOOR("7:30"-MAX(C3,"5:00"),"1:00"),0)
+IF("8:30"-C3>0,FLOOR("8:30"-MAX(C3,"7:30"),"0:30"),0)
+IF(D3-"17:30">0,FLOOR(MIN(D3,"19:30")-"17:30","1:00"),0)
+IF(D3-"19:30">0,FLOOR(MIN(D3,"24:00")-"19:30","0:30"),0))*24
一行につなげて入力してください。
各行は順に、5:00～7:30、7:30～8:30、17:30～19:30、19:30～24:00の
残業時間に対応していますので、作業列として分けた方が
分かりやすいかもしれません。

お礼

遅くなりましたがありがとうございました。
その後条件が変わったり、新しい表が出てきたりしましたがなんとか形になりました。
変更条件―深夜勤務22:00～5:00（30分単位）休憩時間24:00～1:00
私の記入間違いですが、5:00～7:30は30分単位、7:30～8:30は60分単位でした。
新しい表というのはF列-普通残業・G列-深夜残業・H列-早出の項目が出ている表で、人によって使い分けるそうです。
教えて頂いた式を使って深夜残業の列にIF(COUNT(C3:D3)=2,(IFD3-"22:00">0,FLOOR(MIN(D3,"29:00)-"22:00","0:30"),0)-IF(OR(C3>"24:00",D3<"25:00">0),0,"1:00"))*24,"")と入れました。
全てのトータル残業はF2の式で教えて頂いたものに時間を変えて、深夜分をプラスしてできました。
早出分は"0:30"と"1:00"を入れ替えてやりましたが、7:00出勤と入れると1.0と出てきてしまい30分少なくなってしまいましたのでFLOORをCEILINGに変えたら上手くいきました。
しかし私の仕事でもないのにやらされて、「まだ？」と文句まで言われて疲れました。でもエクセルの勉強をしたので我慢しましょう。

回答No.5

takesun

細かい条件が不明ですが(5:00より早い、24:00より遅い場合)、
とりあえずご質問の範囲内で式を作ってみました。

E2=(MIN("17:30",D2)-MAX(C2,"8:30")-"1:00")*24
F2=(FLOOR("8:30"-C2,"1:00")+IF(D2-"19:30">0,FLOOR(D2-"19:30","0:30")+"2:00",FLOOR(D2-"17:30","1:00")))*24

ご参考まで。

補足

ありがとうございます。
その通りに入力してみたら上手くいきましたが、出社時間が7:30で退社時間が16:00の場合に、残業時間にエラーがでてしまいました。
条件は会社の規則によるとその通りになってます。
（5:00より早い、24:00より遅いのは無しです）

回答No.4

tortue2009

画像を間違えたので　再び

回答No.3

tortue2009

まだ勉強中なので　参考になればと思い
条件が複雑なので
できれば残業表のようなものを　別に作り
そこから　持ってくる形は　どうでしょうか？
表さえ作れば
Ｆ２セルに　
=VLOOKUP("8:30"-C2,早出残業分,2,1)+VLOOKUP(D2,残業分,2,1)
表は２つ
１個目は　早出分
　１列目に　８：３０よりどれくらい早いか
　（"8:30"-出勤時間）
　2列目は　その時の会社規程の残業時間
２個目は　残業分
　１列目は　時間そのまま
　２列目は　その時の会社規程の残業時間
そして　表には　名前をそれぞれつける（名前の定義）

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

私も勉強中なので大変参考になりました。
式というのは考え方によってどんな形にもできる物なんですね。
現在試行中です。
ご丁寧にありがとうございました。

回答No.2

rukuku

残業時間の方はK1に
=IF(I1>=TIMEVALUE("8:00"),I1-TIMEVALUE("8:00"),0)
で計算できます。

これで、「勤務時間」と「残業時間」は計算できましたが、ご質問の様に「○○時間」と計算するにはHOUR関数を使います。
E1に
=HOUR(J1)
F1に
=HOUR(K1)
としますと、「○○時間」が得られます。
しかし、分単位は切り捨てになるので、残業時間の「3.5」は得られません。そこで、
F1を
=HOUR(K1*2)/2
としますと、30分（1/2時間）単位での切り捨てになります。

！！！！！注意！！！！！

時間の計算はエクセル内部の「誤差」によって思った結果にならないことがあります。
運用前に十分にテストしてください。

！！！！！注意！！！！！

お礼

2回に渡ってのご説明ありがとうございます。
それも注釈までつけていただいてわかり易かったです。
現在試している所です。
関数の本をみてやっていたのですが、本の通りの条件じゃない場合に
困ってしまって。

回答No.1

rukuku

はじめまして

途中の結果が分かるように、作業用の列を使いながら説明します。

C1に出社時刻、D1に退社時刻が入っているとして、説明します。

まず、「勤務時間」からです。
これは単純にI1に
=D1-C1-"1:00"
で得られます。（休憩時間の１時間を引いています）
ただ、ご質問では、８時間以内を「勤務」時間、それを越えたら「残業時間」とされたいようですので、
J1に
=IF(I1>=TIMEVALUE("8:00"),TIMEVALUE("8:00"),I1)
とします。
計算結果が0.3333…のような少数になってしまった場合には、セルの書式を「時刻」にしてください。
ただし、休憩時間をはさんで勤務したかを考慮していないので、一律に1時間引いています。
数式で判断することもできますが、午前中だけ出社して、その間の休憩時間が１０分というような場合には、手入力で余分に引いた５０分プラスするセルをもうけておいた方が簡単だと思います。

ここでTIMEVALUEという関数が出てきました。
エクセルでは日付や時刻を「シリアル値」という数値に置き換えています。
J1の計算ではエクセルが「これは時刻だ！」と判断して計算しましたが、関数の中では上手く判断できないこともあります。そのときには、時刻を「シリアル値」に変換してから計算します。

シリアル値については以下のサイトを参照してください。
http://support.microsoft.com/kb/882391/ja
http://support.microsoft.com/kb/880979/ja
http://support.microsoft.com/kb/401659/ja