時間からどうやって求めたらいいですか？

質問

少し前から簡単な給与計算をエクセルで作っております。

入力項目は
名前/出社時間/退社時間/一日の労働時間合計
Ａ子　8：15　19：00　9.5(h)
Ｂ男　8：15　17：15　8.0(h)

現状問題計算式で困っているが
遅刻と早退です。
1，定時が8：15、17：15で中途半端。間に10：00-10：07　12：00-12：45　15：00-15：08　の休憩時間は時間計算より除外したい。
2，一日の内で遅刻と早退両方されると、遅刻何時間、早退何時間と振り分け計算がよくわからない。
3，出来れば分単位でなく、30分単位で分かりたい。下記30分刻み表
　　8：15-8：45-9：15-9：45-10：30-11：00-11：30-12：00-13：15-13：45-14：15-14：45-15：15-15：45-16：15-16：45-17：15

例
Ｂ男　8：40　14：50　5.0(h）　という入力より

遅刻時間　0.5h　早退時間　2.5h　　　という答えをエクセルで引き出したいのです。

どうぞご教授のほどお願いします。

回答No.1

KURUMITO · ベストアンサー

遅刻時間は次の式で

=IF(OR(B2="",B2<="8:15"*1),"",(IF(CEILING(B2,"0:30")-"0:15">=B2,CEILING(B2,"0:30")-"0:15",CEILING(B2,"0:30")+"0:15")-"8:15")*24)

早退時間は次の式で

=IF(OR(C2="",C2>="17:15"*1),"",("17:15"-IF(FLOOR(C2,"0:30")+"0:15">C2,FLOOR(C2,"0:30")-"0:15",FLOOR(C2,"0:30")+"0:15"))*24)

なお、セルの書式設定で表示形式は標準にします。

お礼

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

補足

色々試してみたのですが、
体調悪くて遅刻9：00　そのまま11：30にDOWNして帰った場合や
昼から（13：00）に来て16：00に早退した場合などがうまくでません。

関数を解読してトライしてみます。

回答No.6

tom04

No.3・5です！

今回は全く別案です。
↓の画像で上側がSheet2になります。
これは単純に　出社時刻・退社時刻　による休憩時間を表にしただけです。
Sheet1の労働時間の部分で使用するために作ってみました。
（退社時刻－出社時刻－表内の休憩時間）とするため・・・

Sheet1のD2セルに
=IF(COUNTBLANK(B2:C2),"",FLOOR(C2-B2-INDEX(Sheet2!$B$2:$H$8,MATCH(B2,Sheet2!$A$2:$A$8,-1),MATCH(C2,Sheet2!$B$1:$H$1,-1)),"0:30")*24)

E2セルに
=IF(COUNTBLANK(B2:C2),"",IF(B2>TIMEVALUE("8:15"),CEILING(B2-"8:15","0:30")*24,""))

F2セルに
=IF(COUNTBLANK(B2:C2),"",IF(C2<"17:15",CEILING("17:15"-C2,"0:30")*24,""))

という数式を入れ、D2～F2セルを範囲指定　→　F2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　Sheet2の休憩時間は正確かどうか判りませんので、今一度確認してみてください。
※　労働時間重視でやっていますので、遅刻・早退はイマイチ自信がありません。

お役に立てば良いのですが・・・m(_ _)m

お礼

本当に色々と検討して頂きありがとうございます！
とても嬉しいです。

補足

ありがとうございます。
別理由により、労働時間は手入力が必須であり、現状は時間から遅刻、早退を引っ張りたい感じです。

皆様の式を一つづつ式の意味を調べてなんとか
C3出勤時間
B22　10：15
B23　12：15
T3　別の関数式で遅刻かどうかの判定のみ

I3に
=IF(T3="遅",IF(AND(C3>$B$22,C3<$B$23),((CEILING(C3,"0:30")-"8:30")*24),((IF(CEILING(C3,"0:30")-"0:15">=C3,CEILING(C3,"0:30")-"0:15",CEILING(C3,"0:30")+"0:15")-"8:15")*24)),"")

J3に
=IF(C3>=$B$23,I3-1,I3)

で二つのセルを使うことでなんとか遅刻を想定範囲で切り替えれるようになりました。
なんとか一つのセルに出来ればと思うのですが、長すぎて（）が足りないのかすぐエラーが出ます。
もう一歩のところで詰まってしまってます。

回答No.5

tom04

No.3です！
たびたびごめんなさい。

前回の投稿で間違いがありました。
Sheet1のH2セル（休憩時間合計）の数式を
=IF(COUNTBLANK(B2:C2),"",VLOOKUP(C2,Sheet2!$D$2:$E$5,2,1)-VLOOKUP(B2,Sheet2!$C$2:$E$5,3,1))

に訂正してください。
前回の数式では
仮に12：00退社の人の場合、昼休憩を取っていないにも関わらず
午前中の休憩7分と昼休憩45分を合計した52分を引く計算になっていました。

何度も失礼しました。m（_ _）m

お礼

みなさん本当にありがとうございます。
即日の内にこんなに回答がいただけるなんて感激です。

毎日のシートがあり、そこから個々のシートに引っ張って集計しております。
定時時間や休憩時間は個々のシート内に記載しておりましたので、
そこに累計休憩時間を足させて頂きアレンジして頑張ってみます。

午前9：00出社　11：00退社
午後13：00出社　16：00退社
など、労働時間は問題無いのですが、遅刻時間、相対時間が昼休憩を挟むと狂ってしまうのですが何かいい方法ありますでしょうか？

上記のような場合はレアケースですが、体調不良で遅刻出勤して午前中にダウンとかいうのも実例でありましたので、そのような場合にも対応できれば素敵だなぁと。

我侭で申し訳ありません。

よろしければご教授の程宜しくお願い致します。

回答No.4

noname#204879

休憩時間内に出社/退社した人はどうする？
12:00 に出社/退社した人の遅刻/早退時間は、それぞれ 4.0(h)/5.5(h)
12:45 に出社/退社した人の遅刻/早退時間は、それぞれ 4.5(h)/4.5(h)
でＯＫですか？

上記の遅刻/早退時間には、別の休憩時間が含まれているがそれでＯＫですか？

補足

12:00に出勤しても、12：45に出勤しても休憩中ですのでカウントはしません。
労働時間は12：45スタートの計算になります。

お返事が遅くなりすみません。
基本休憩時間は計算から抜きたいです。

回答No.3

tom04

こんにちは！
一例です。
出社時刻が休憩時間以降・退社時刻が休憩時間前の場合も
考慮しなくてはならないと思いますので・・・

案として↓の画像のようにSheet2に表を作成しておきます。
Sheet2のE列は労働時間から休憩時間を引くために設けています。
Sheet1のH列に出社・退社時刻によって休憩時間をどれれか引けば良いか？を表示させておきます。
H2セルに
=IF(COUNTBLANK(B2:C2),"",VLOOKUP(C2,Sheet2!$C$2:$E$5,3,1)-VLOOKUP(B2,Sheet2!$C$2:$E$5,3,1))
という数式を入れオートフィルでずぃ～～～！っと下へコピー！

D2セルに
=IF(COUNTBLANK(B2:C2),"",FLOOR(C2-B2-H2,"0:30")*24)
E2セルに
=IF(COUNTBLANK(B2:C2),"",IF(B2>Sheet2!$A$2,CEILING(B2-Sheet2!$A$2,"0:30")*24,""))
F2セルに
=IF(COUNTBLANK(B2:C2),"",IF(C2<Sheet2!$B$2,CEILING(Sheet2!$B$2-C2,"0:30")*24,""))
（D2～F2セルの表示形式は「標準」で！）
という数式を入れ、D2～F2セルを犯意指定　→　F2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

※　一発でご希望の方法とはいかないと思いますが、
参考になりますかね？m（_ _）m

回答No.2

KURUMITO

ごめんなさい。回答Ｎｏ1です。先ほどの式ではＢ２セルに出社時刻、Ｃ2セルに退社時刻が入力されているとした式です。