【エクセル】休憩時間をマイナスし、実働時間の計算

質問

社員のタイムカードをエクセルに打ち込んで管理することになりました。
(お給料にかかわるものではなく、統計目的です。)
上司から「何時に出社し、何時に帰社し、実働何時間かをひと目でわかるようにお願いね。」と言われたのですが…その「実働時間」で悩んでいます。
タイムカードをつけている社員は皆、出来高制での契約の人たちのため、出社時間も帰社時間もバラバラです。５分単位で記録します。定められた休憩時間は10時から15分、お昼に45分、15時から15分です。(この時間は必ず休憩としてカウントするため、例えば9時50分に出社してきた社員も、10時からの15分間は休憩としてカウントしなくてはなりません。)
以上のことを考慮して表を作ると…実働時間を計算するのが難しくて…。問題は、この定められた休憩時間にその社員が出社していたか否かで休憩時間が違ってくる点です。お昼を挟まないことは滅多に無いので、それは例外として個々に処理しても良いのですが、10時と15時が大問題なのです。一人一人計算すれば済む話ではあるのですが、これから半年前まで遡って入力を始めるので出来れば出社・帰社時間を入力するだけで実働時間が表示されるように入力フォームを作りたいと思っています。

名前　09　50　16　00　実働時間
名前　11　00　18　30　実働時間

このような表が理想です。一列で9：50とせず、二列使って09 50としたいのです。なにか上手い方法は無いでしょうか？朝から考えているのですがいい方法が思いつきません…
長くなりましたが上手く説明出来ているか不安です。
不明な点は補足します。よろしくお願いします。

回答No.4

keirika · ベストアンサー

名前をＡ列、出社時間(時)をＢ列、出社時間(分)をＣ列、退社時間(時)をＤ列、退社時間(分)をＥ列、実働時間をＦ列に求めると仮定した場合

F1セルに=TIME(D1,E1,0)-TIME(B1,C1,0)-IF(AND(TIME(B1,C1,0)<=TIME(10,0,0),TIME(D1,E1,0)>=TIME(10,0,0)),TIME(0,15,0),0)-IF(AND(TIME(B1,C1,0)<=TIME(12,0,0),TIME(D1,E1,0)>=TIME(12,0,0)),TIME(0,45,0),0)-IF(AND(TIME(B1,C1,0)<=TIME(15,0,0),TIME(D1,E1,0)>=TIME(15,0,0)),TIME(0,15,0),0)

を入力し、下方向へコピーして下さい。

ただ、少し条件があります。まず、出社時間、退社時間とも数値として入力されていること、休憩時間中の出社、退社が無いことです。

もしよければ、一度試してみてください

お礼

お礼が大変遅くなりました。
やっと、私の手元にデータがかえって参りました。
さっそくやってみたのですが、
で、出来ました～～!!!最初AMが付いてしまってダメかと思いましたが単なるセル設定で解決しました！
本当にありがとうございます(TT)助かりました!!

（愚痴）と、いうか・・・こんな半月も私の手元を離れるなら「これ最優先で！」なんていわないでよ～と密かに思ってしまったりして…。（愚痴）どうしようか悩みぬいた私の半日を返して!!（愚痴）

でも、おかげで良い回答にめぐり合えてよかったです。
keirika様、(たぶん私のせいでうまくいかなかった)他の回答を下さった方々、本当にありがとうございました！

補足

上司に「あっチョット待って！」とデータごと持っていかれてしまい…検証が遅れそうです…(--;)後日検証後改めてお礼します＞＜;;

回答No.3

shinkun0114

●表示はともかく計算は時刻形式を使う

＞一列で9：50とせず、二列使って09 50としたいのです。

　とのことですが、これでは何かと計算がしにくいので、いったんエクセルの
時刻形式のシリアル値に直して計算を行い、最後に表示のみ２列になおしましょう。
途中の計算に使った列は表示しないようにしておけば、問題ありません。

　時・分それぞれの数値を時刻のシリアル値に直すには、TIME関数を使います。
・出勤時刻
　=TIME([出勤時のセル],[出勤分のセル],0)
・退勤時刻
　=TIME([退勤時のセル],[退勤分のセル],0)

　これをベースに労働時間を計算し、最後に時・分に戻してやります。

●休憩時間の算定法
　まず10時の休憩を算定します。
　10時以前に出社した人は15分。10:15以降に出社した人は0分です。
　それ以外の場合は、10:15から出社時刻を引いて求めます。

・10時の休憩時間
　=IF([出勤時刻]<TIME(10,0,0),TIME(0,15,0),IF([出勤時刻]>TIME(10,15,0),0,TIME(10,15,0)-[出勤時刻]))

　同様に15時も求めます。

・15時の休憩時間
　=IF([退勤時刻]>TIME(15,15,0),TIME(0,15,0),IF([退勤時刻]<TIME(15,10,0),0,[退勤時刻]-TIME(15,15,0)))

　これらをまずセルに入れて計算しておきましょう。

●労働時間の計算
　=[退勤時刻]-[出勤時刻]-[10時の休憩時間]-[15時の休憩時間]-time(0,45,0)

●労働時間を２列に変える

・労働時間「時」セル
　=HOUR([労働時間])

・労働時間「分」セル
　=MINUTE([労働時間])

●計算に使った列を非表示にする
　[出勤時刻][退勤時刻][10時休憩][15時休憩][労働時間]
　これらが入ったセルを非表示にします。

お礼

すみません、お礼が大変遅くなりました。

＞いったんエクセルの時刻形式のシリアル値に直して計算を行い、最後に表示のみ２列になおしましょう。

なるほど～!!と思ったのですが（時間を２列に分けて表記することも悩みのタネの一つだったので）時刻形式のシリアル値が一行に一列だけしか表示できなくて…
（EXCEL2003…そんなルールありましたっけ!?何か変な設定したのかな、私…）
出勤時刻をシリアル値にすると退勤時刻が化けて、
退勤時刻をシリアル値にすると出勤時刻が化けます…。
せっかくご丁寧に説明していただいたのにすみません…。
ご回答ありがとうございました！

補足

至急検証してみます！多少お礼が遅れるかもしれませんがご了承下さい＞＜;

回答No.2

mshr1962

休憩時間を含む実働時間=(TIME(D2,E2,0)-TIME(B2,C2,0)+AND(D2>=24,B2<24)
10時の判定=IF(AND(B2<10,D2>=10),"0:15",0)
お昼の判定=IF(AND(B2<12,D2>=13),"0:45",0)
15時の判定=IF(AND(B2<15,D2>=15),"0:15",0)
後は最初の結果から後の結果を引くだけ
1セルで行ってもいいし5つのセルを使ってもok

お礼

お礼が大変遅くなりました。
【休憩込み】【休憩時間】【引き算】として３セル使って試してみたのですが、休憩時間の計算結果が【＃VALUE!】に…。
せっかくご回答いただいたのにすみません。
（たぶん私がおかしな事をしたのでしょう…。でも、それを見つけられるほどの能力はまだ無いみたいです…）
ご回答、ありがとうございました！

補足

至急検証してみます！多少お礼が遅れるかとは思いますがご了承ください＞＜

回答No.1

meitoku

エクセルの関数を使ってみてはいかが？
「時間の計算」で色々あります。
理解できる簡単な物を探してください。

参考URL：

http://support.microsoft.com/default.aspx?scid=kb;ja;416574

お礼

さっそくのご回答ありがとうございます。
休憩時間を入力する欄を設け、一つ一つ休憩時間を入力していけば可能かとは思いましたが、皆さん毎日トータルの休憩時間が違うので、出社・退社時間から休憩時間を判断してくれたら助かるなぁ～と思っているのです…。
やっぱり不可能でしょうか…？