エクセルで残業時間を時間帯によって＋１時間＋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 回答日時： 2009/04/14 01:09

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に変えたら上手くいきました。
しかし私の仕事でもないのにやらされて、「まだ？」と文句まで言われて疲れました。でもエクセルの勉強をしたので我慢しましょう。

お礼日時：2009/04/18 14:06

No.5 回答者： takesun 回答日時： 2009/04/12 17:39

細かい条件が不明ですが(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より遅いのは無しです）

補足日時：2009/04/13 12:39

No.4 回答者： tortue2009 回答日時： 2009/04/12 14:26

画像を間違えたので　再び

No.3 回答者： tortue2009 回答日時： 2009/04/12 14:22

まだ勉強中なので　参考になればと思い

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

この回答へのお礼

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

お礼日時：2009/04/13 12:55

No.2 回答者： rukuku 回答日時： 2009/04/12 13:58

残業時間の方は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回に渡ってのご説明ありがとうございます。
それも注釈までつけていただいてわかり易かったです。
現在試している所です。
関数の本をみてやっていたのですが、本の通りの条件じゃない場合に
困ってしまって。

お礼日時：2009/04/13 12:51

No.1 回答者： rukuku 回答日時： 2009/04/12 13:36

はじめまして

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

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