[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『定時間内の勤務時間が知りたい (計算式長すぎ)』(勤怠管理)
度々ご質問させて頂きます。
定時間(8:00~17:00)内の勤務時間が知りたいです。
休 憩 10:00から10分 15:00から10分 昼休憩 12:00から1時間 (遅刻・早退・中抜は12:30で出勤・退勤があります) CEILING/FLOOR関数で休憩時間中の出勤・退勤は表示されませんが、昼休憩中の遅刻・早退・中抜は12:30のみで表示されます。
A1 出勤
B1 中抜
C1 中入
D1 退勤
E1 定時間の勤務時間を表示
現在
E1 =IF(A1="","",IF(B1="",IF(A1="","",IF(OR(D1<"8:00"*1,A1>"10:00"*1),0,MIN("10:00",D1)-MAX("8:00",A1)))+IF(D1="12:30"*1,(IF(OR(D1<"10:10"*1,A1>"12:00"*1),0,MIN("12:00",D1)-MAX("10:10",A1)))+"0:30"*1,(IF(OR(D1<"10:10"*1,A1>"12:00"*1),0,MIN("12:00",D1)-MAX("10:10",A1))))+IF(A1="12:30"*1,(IF(OR(D1<"13:00"*1,A1>"15:00"*1),0,MIN("15:00",D1)-MAX("13:00",A1)))+"0:30"*1,(IF(OR(D1<"13:00"*1,A1>"15:00"*1),0,MIN("15:00",D1)-MAX("13:00",A1))))+(IF(OR(D1<"15:10"*1,A1>"17:00"*1),0,MIN("17:00",D1)-MAX("15:10",A1))),IF(A1="","",IF(OR(B1<"8:00"*1,A1>"10:00"*1),0,MIN("10:00",B1)-MAX("8:00",A1)))+IF(B1="12:30"*1,(IF(OR(B1<"10:10"*1,A1>"12:00"*1),0,MIN("12:00",B1)-MAX("10:10",A1)))+"0:30"*1,(IF(OR(B1<"10:10"*1,A1>"12:00"*1),0,MIN("12:00",B1)-MAX("10:10",A1))))+IF(A1="12:30"*1,(IF(OR(B1<"13:00"*1,A1>"15:00"*1),0,MIN("15:00",B1)-MAX("13:00",A1)))+"0:30"*1,(IF(OR(B1<"13:00"*1,A1>"15:00"*1),0,MIN("15:00",B1)-MAX("13:00",A1))))+(IF(OR(B1<"15:10"*1,A1>"17:00"*1),0,MIN("17:00",B1)-MAX("15:10",A1)))+IF(A1="","",IF(OR(D1<"8:00"*1,C1>"10:00"*1),0,MIN("10:00",D1)-MAX("8:00",C1)))+IF(D1="12:30"*1,(IF(OR(D1<"10:10"*1,C1>"12:00"*1),0,MIN("12:00",D1)-MAX("10:10",C1)))+"0:30"*1,(IF(OR(D1<"10:10"*1,C1>"12:00"*1),0,MIN("12:00",D1)-MAX("10:10",C1))))+IF(C1="12:30"*1,(IF(OR(D1<"13:00"*1,C1>"15:00"*1),0,MIN("15:00",D1)-MAX("13:00",C1)))+"0:30"*1,(IF(OR(D1<"13:00"*1,C1>"15:00"*1),0,MIN("15:00",D1)-MAX("13:00",C1))))+(IF(OR(D1<"15:10"*1,C1>"17:00"*1),0,MIN("17:00",D1)-MAX("15:10",C1)))))
で、見直すときにまるっきりわかりません。
簡単には出来ないでしょうか?
< 使用 Excel:Excel2013、使用 OS:Windows7 >
> 昼休憩 12:00から1時間 (遅刻・早退・中抜は12:30で出勤・退勤があります) >CEILING/FLOOR関数で休憩時間中の出勤・退勤は表示されませんが、昼休憩中の遅刻・早退・中抜は12:30のみで表示されます。
この部分がちょっと分かりません。
その説明に合うサンプルを提示してください。
(半平太) 2017/02/23(木) 23:32
一つの数式でやろうとするとゴチャゴチャするので、 休み時間を別に計算した方が分かり易いと思います。
以下G:L列に休憩時間を算出する案 (中抜きが無い場合は、出勤と中抜きは同時刻として計算する)
(1) G2セル =MAX(0,MIN(H$1,IF($B2="",$A2,$B2))-MAX(G$1,$A2)) (2) H2セル =MAX(0,MIN(H$1,$D2)-MAX(G$1,IF($C2="",$A2,$C2)))
「G2:H2」を一括選択して、L2セルまでフィルコピーする
(3) E2セル =IF(OR(COUNT(A2:D2)={2,4}),B2-A2+MIN("17:00",D2)-C2-SUM(G2:L2),"")
<結果図> 1行目に休憩時間帯を記入する┐ │ 行 __A__ __B__ __C__ __D__ ________E________ _F_ __G__ __H__ __I__ __J__ __K__ _ L _ │ 1 出勤 中抜 中入 退勤 定時間の勤務時間 10:00 10:10 12:00 13:00 15:00 15:10 ←┘ 2 8:00 12:30 3:50 0:10 0:30 3 8:00 12:30 15:00 21:30 5:40 0:10 0:30 0:10 4 8:00 18:00 7:40 0:10 1:00 0:10
出-中 入-退 出-中 入-退 出-中 入-退
(半平太) 2017/02/24(金) 10:34
(勤怠管理) 2017/02/25(土) 17:29
>(3) E2セル =IF(OR(COUNT(A2:D2)={2,4}),B2-A2+MIN("17:00",D2)-C2-SUM(G2:L2),"") ↓ E2セル =IF(OR(COUNT(A2:D2)={2,4}),B2-MAX("8:00",A2)+MIN("17:00",D2)-C2-SUM(G2:L2),"")
(半平太) 2017/02/25(土) 18:36
長さ0の文字列が入ると直接計算できなくなります。
N()関数を活用して、数値の「0」へ変換してください。(元々数値データなら無影響です)
E2セル =IF(OR(COUNT(A2:D2)={2,4}),N(B2)-MAX("8:00",A2)+MIN("17:00",D2)-N(C2)-SUM(G2:L2),"") ~↑~ ~↑~
(半平太) 2017/02/26(日) 06:42
N( ) 関数で無事表示できました。
丸め作業は完了し、所定時間内の労働時間もお陰様でできました。
次は総労働時間と所定時間外の労働時間を計算して行きたいと思います。
行詰まった時またご教授願えましたらお力添えをお願いしたいです。
よろしくお願いします。
(勤怠管理) 2017/02/27(月) 10:22
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.