[[20050830111110]]　『タイムカードの計算方法について』（さちよん）　ページの最後に飛ぶ

『タイムカードの計算方法について』（さちよん）

いつもお世話になっております。
初めての経験でさっぱりわからなく、過去ログを検索してみたのですが、
自分のものと当てはまるものがみあたらなく困っています。
時間の算出の方法について教えてください。

　　　A　　　　　　　　　B　　　　　　　　　C　　　　　　　　　D　　　　　　　　E　　　　　　　　F　　　　　　G　

1　　出勤時間　　退社時間　　　　　休憩時間　　　実働時間　　　通常残業　　　深夜残業

2　　9：22　　　　18：36

3　　9：10　　　　18：49

4　　9：28　　　　8：57

5　　8：57　　　　19：12

6　　9：24　　　　22：32

7　　9：26　　　　24：01

8　　10：58　　　　19：32

9　　　　　　　　合計

定時は、9：30～18：30で出勤は9：30前に来ても9：30からとする。

9：30～18：30の間で昼休み1時間、18：30からの15分間、22：00からの15分間、3：15からの15分間は必然的に実務時間からひかれる。

18：45～22：00までは通常残業。22：00以降は深夜残業とする。

として

①C列に休憩時間の合計

②D列にはC列休憩時間を拘束時間からひいた実働時間を求める

③E列は通常残業時間をもとめる

④F列は深夜残業時間をもとめる

⑤C9,D9,E9,F9にはそれぞれの合計時間を求めたい。

単位は、18：00のようにしたい。

どうぞよろしくお願いします。(さちよん)

 それぞれの計算式を作るのは大変なので、各時間区分における時間を計算し、必要な部分を合計したらどうでしょう。
 作業列として、D～L列を使用しています。見たくないならグループ化して折りたためばよいと思うのですが・・・
 3～4行目に時間区分を入力します。表示形式は[h]:mmとし、24時以降は25:00のような表記とします。
 D5：=IF(OR(COUNT($B$5:$C$5)<2,$B5-D$4>=0,$C5-D$3<=0),"",TEXT(MIN($C5,D$4)-MAX($B5,D$3),"h:mm")*1)
 として、L5までコピーし、更に下方向に必要行分コピーします。
 M5:=IF(OR(COUNT(B5:C5)<2,SUM(E5,G5,I5,K5)=0),"",SUM(E5,G5,I5,K5))
 N5:=IF(OR(COUNT(B5:C5)<2,SUM(D5,F5,H5,J5,L5)=0),"",SUM(D5,F5,H5,J5,L5))
 O5:=IF(OR(COUNT(B5:C5)<2,SUM(H5)=0),"",SUM(H5))
 P5:=IF(OR(COUNT(B5:C5)<2,SUM(J5,L5)=0),"",SUM(J5,L5))

 分単位で計算するには
 D5:=IF(OR(COUNT($B$5:$C$5)<2,ROUND($B5*1440,0)-ROUND(D$4*1440,0)>=0,ROUND($C5*1440,0)-ROUND(D$3*1440,0)<=0),"",
TEXT((MIN(ROUND($C5*1440,0),ROUND(D$4*1440,0))-MAX(ROUND($B5*1440,0),ROUND(D$3*1440,0)))/1440,"h:mm")*1)
 こんな感じで合っていますか？
 # Excelからコピペしたため、B2セルから始まっていますのでご注意ください。
　　(Hatch)
　
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	出勤	退勤	定時1	休憩1	定時2	休憩2	残業	休憩3	深夜1	休憩4	深夜2	休憩時間	実働時間	通常残業	深夜残業
3		開始時間	9:30	12:00	13:00	18:30	18:45	22:00	22:15	27:15	27:30				
4		終了時間	12:00	13:00	18:30	18:45	22:00	22:15	27:15	27:30	33:00				
5	9:22	18:36	2:30	1:00	5:30	0:06						1:06	8:00		
6	9:10	18:49	2:30	1:00	5:30	0:15	0:04					1:15	8:04	0:04	
7	9:28	32:57	2:30	1:00	5:30	0:15	3:15	0:15	5:00	0:15	5:27	1:45	21:42	3:15	10:27
8	8:57	19:12	2:30	1:00	5:30	0:15	0:27					1:15	8:27	0:27	
9	9:24	22:32	2:30	1:00	5:30	0:15	3:15	0:15	0:17			1:30	11:32	3:15	0:17
10	9:26	24:01	2:30	1:00	5:30	0:15	3:15	0:15	1:46			1:30	13:01	3:15	1:46
11	10:58	19:32	1:02	1:00	5:30	0:15	0:47					1:15	7:19	0:47

 詳しいご説明ありがとうございます。
教えていただいた関数を使わせていただきました。
1点書き忘れた項目がありまして、残業計算のところで、残業代の発生が5分刻みなんですがどのように関数に組み込んだらいいでしょうか？
例えば、退社時間が19：04だと残業代の計算は19：00まで、23：16だと23：00という具合になります。H列、J列、L列に設定をしたいのですがよろしくお願いします。(さちよん)

 >5分刻みなんですが
 >19：04だと残業代の計算は19：00まで
 >23：16だと23：00という具合になります
 矛盾してます。「15」分でしょうか？
 O,P列で丸めたらどうでしょう?
 O5:=IF(OR(COUNT(B5:C5)<2,SUM(H5)=0),"",TEXT(FLOOR(H5,"0:15"),"h:mm")*1)
 P5:=IF(OR(COUNT(B5:C5)<2,SUM(J5,L5)=0),"",TEXT(FLOOR(TEXT(SUM(J5,L5),"h:mm")*1,"0:15"),"h:mm")*1)
 なお、残業が15分未満なら「0:00」と表示され、0なら空白になります。
 　　(Hatch)

 説明不足と入力ミスで重ね重ね申し訳ありませんm(__)m
　
 >19：04だと残業代の計算は19：00まで
 >23：16だと23：15という具合になります
　
です。申し訳ありません。
　
教えていただいたものをいれてみたところ
G列の休憩時間は18：30～18：45までに退勤すると休憩とはみなされないので『0：00』と表示させたく、I列、K列も同様にしたいのです。
また、
　
休憩時間　M5:=IF(OR(COUNT(B5:C5)<2,SUM(E5,G5,I5,K5)=0),"",SUM(E5,G5,I5,K5))
　
実務時間　N5:=IF(OR(COUNT(B5:C5)<2,SUM(D5,F5,H5,J5,L5)=0),"",SUM(D5,F5,H5,J5,L5))
　
に5分以下は切り捨て(『8：04』は『8：00』に)と設定したいのですが、どのように付け加えたらいいでしょうか？　(さちよん)

 理解できていませんが、退勤時刻を15分単位で丸めると読み取りました。
 よって元の計算の方を変更してみました。それに伴い、M～P列も変更します。
 D5:=IF(OR(COUNT($B$5:$C$5)<2,ROUND($B5*1440,0)-ROUND(D$4*1440,0)>=0,ROUND($C5*1440,0)-ROUND(D$3*1440,0)<=0),"",
TEXT((MIN(FLOOR(ROUND($C5*1440,0),15),ROUND(D$4*1440,0))-MAX(ROUND($B5*1440,0),ROUND(D$3*1440,0)))/1440,"h:mm")*1)
 L5までコピーし、更に下方向へコピーします。
 M5:=IF(OR(COUNT(B5:C5)<2,SUM(E5,G5,I5,K5)=0),"",TEXT(SUM(E5,G5,I5,K5),"h:mm")*1)
 N5:=IF(OR(COUNT(B5:C5)<2,SUM(D5,F5,H5,J5,L5)=0),"",TEXT(SUM(D5,F5,H5,J5,L5),"h:mm")*1)
 O5:=IF(OR(COUNT(B5:C5)<2,SUM(H5)=0),"",TEXT(SUM(H5),"h:mm")*1)
 P5:=IF(OR(COUNT(B5:C5)<2,SUM(J5,L5)=0),"",TEXT(SUM(J5,L5),"h:mm")*1)
 # 出社時刻はこの様な丸め処理は要らないのでしょうか?
 　　(Hatch)

返信遅くなりました。
回答ありがとうございます。
いろいろ自分でもいじってみます。
また行き詰ってしまったらご指導よろしくお願いします。(さちよん)

コメント返信：

[ 一覧(最新更新順) ]

[[20050830111110]] 『タイムカードの計算方法について』（さちよん） ページの最後に飛ぶ

[[20050830111110]]　『タイムカードの計算方法について』（さちよん）　ページの最後に飛ぶ