[[20170223205526]]　『定時間内の勤務時間が知りたい (計算式長すぎ)』（勤怠管理）　ページの最後に飛ぶ

『定時間内の勤務時間が知りたい (計算式長すぎ)』（勤怠管理）

度々ご質問させて頂きます｡
定時間(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

半平太さん有難う御座います｡
すみません｡私の勘違いで休憩時間の労働を所定時間労働に入れていました｡
単純に 8:00～17:00 の時間内で休憩時間(10:00～10:10 12:00～13:00 15:00～15:10)を除いた時間が表示したいです｡
例 1
A1=8:00 B1･C1=空白 D1=12:30 (12:30で早退) E1=3:50
　(所定3:50 法内残業0:30 不就労3:50)
例 2
A1=8:00 B1=12:30 C1=15:00 D1=21:30 (12:30から15:00まで中間外出) E1=5:40
　(所定5:40 法定内残業2:20 法定外残業2:40 不就労2:00)
こんな感じです｡
説明不足で申し訳有りませんが､何か有りましたらコメントお願いします。
（勤怠管理） 2017/02/24(金) 00:15

 一つの数式でやろうとするとゴチャゴチャするので、
 休み時間を別に計算した方が分かり易いと思います。

 以下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

（半平太）さん　早速の回答有難う御座います。
無事表示できました｡
集計表はまだ作り始めなのでまた何か有りましたら
お力添え頂けると嬉しいです。
有難う御座いました。
（勤怠管理） 2017/02/25(土) 19:10

（半平太）さん　お力添えお願いしたいのですが。
E2セル =IF(OR(COUNT(A2:D2)={2,4}),B2-A2+MIN("17:00",D2)-C2-SUM(G2:L2),"")
の A2,B2,C2,D2 にはIFERROR関数で丸め作業をしたいのですが、B2,C2 が空白状態になると
「#VALUE!」のｴﾗｰが表示されます｡原因は何でしょうか?
よろしくお願いします。普通の空白であれば正常に表示します。
何とかなりますか?
（勤怠管理） 2017/02/26(日) 01:07

 長さ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

コメント返信：

[ 一覧(最新更新順) ]

[[20170223205526]] 『定時間内の勤務時間が知りたい (計算式長すぎ)』（勤怠管理） ページの最後に飛ぶ

[[20170223205526]]　『定時間内の勤務時間が知りたい (計算式長すぎ)』（勤怠管理）　ページの最後に飛ぶ