• ベストアンサー
  • 困ってます

勤務時間の計算

勤務時間 8:00~19:00 の間なら何時間でも働いてよくて 昼休み12:00~13:00の時間帯は必ず休みになります。 9:30出社 17:30退社 の場合、7時間となり 9:30出社 11:30退社 の場合 2時間となり、 14:00出社 18:00退社 の場合 4時間の勤務となります。 エクセルで     A      B      C 1  勤務時間    昼休み 2   8:00      12:00 3   19:00     13:00 4  出社       退社    勤務時間 5   9:30      17:30    7:00 6   9:30      11:30    2:30 ↓ドラッグ 7  14:00      18:00    5:00 C5に =IF(COUNT(A5:B5)<2,"",TEXT(MAX(0,(MIN($B5,A$3)-MAX($A5,A$2))-(MIN($B5,B$3)-MAX($A5,B$2))),"h:mm")*1) と入れてC7までドラッグすると、 C6が2時間のところが2:30になり、 C7が4時間が正しいのに5時間になってしまい、うまく計算されません。 うまく計算されるには、どのような式にすればいいのでしょう? もしくはもっと簡単な関数があるのでしょうか? ご教授、よろしくお願いいたします。

共感・応援の気持ちを伝えよう!

  • 回答数5
  • 閲覧数155
  • ありがとう数7

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.4

 C5セルに次の数式を入力してから、C5セルをコピーして、C6以下に貼り付けると良いと思います。 =IF(COUNT($A5,$B5)=2,($A5<$B$2)*(MIN($B$2,$B5+($A5>$B5))-$A5)+($B5+($A5>$B5)>$B$3)*($B5-MAX($B$3,$A5)),"")

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答、ありがとうございます。 こちらもシンプルでいいですね!

関連するQ&A

  • Excel:当日勤務時間の計算式のアドバイスを下さい

    Excel2002を使用し、勤務表を作成しています。 修正を繰り返しだいぶ計算ができるようになりました。 今回は、開始13:15終了16:15の場合、休憩なし、当日勤務時間3時間と計算させたいのですが、何かアドバイスあればよろしくお願いします。 現在使用している計算式では以下のように計算されます。 開始8:30終了17:05、休憩45分、当日勤務時間7時間50分※定時 開始7:30終了16:05の場合、休憩45分、当日勤務時間7時間50分 開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分 開始13:15終了17:05の場合、休憩なし、当日勤務時間3時間50分 開始13:15終了17:50の場合、休憩なし、当日勤務時間3時間50分 -------------------------------------------------------- 開始時刻:C列 終了時刻:D列 開始時刻を15分単位で切り上げる:P列:=CEILING(C6,"0:15") 終了時刻を15分単位で切り上げる:Q列:=CEILING(D6,"0:15") 休憩時間を45分として勤務時間を計算する:R列 -------------------------------------------------------- ■休憩時間 =IF(COUNT(C6:D6)<2,0,IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45",IF ((C6>="13:15"*1)*(D6>="17:20"*1),"0:15","0:00")))+IF(R6>="8:00"*1,"0:15","0:00") ■当日 勤務時間 =IF(COUNT(C6:D6)<2,0,IF((C6>="13:15"*1)*(D6>="17:20"*1),R6-"0:15",IF(R6>="8:00"*1,R6-"0:15",R6))) ■休憩時間を45分として勤務時間を計算する =IF(COUNT(C6:D6)<2,0,AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6),"16:05")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"07:30")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX(D6,"16:05")-"16:04:59","0:15")))

  • 勤務時間の計算方法

    勤務時間の計算を、以下のようにやりたいのですが、可能でしょうか? A1=出社時間 B1=退社時間 C1=1日の勤務時間 ※昼休憩12:00~13:00と夜休憩18:00~18:30は休憩時間となります。 出社時間が日によって異なるため、どのように休憩時間を 引けばよいのか悩んでいます。 よろしくお願いします。

  • Excel:勤務時間計の計算式がうまくいかない

    Excel2002を使用し、勤務表を作成しています。 以前こちらでアドバイスをいただき、作成することができました。 今回修正で、開始8:30終了17:05の場合、休憩45分、当日勤務時間7時間 50分と計算させたいのですが、何かアドバイスあればよろしくお願いし ます。 現在使用している計算式では以下のように計算されます。 開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分 開始8:30終了17:05の場合、休憩60分、当日勤務時間7時間35分 ■8時-16時35分(定時)の場合 開始時刻:C列 終了時刻:D列 開始時刻を15分単位で切り上げる:P列:=CEILING(C7,"0:15") 終了時刻を15分単位で切り上げる:Q列:=CEILING(D7,"0:15") ■休憩時間 =IF(COUNT(C7:D7)<2,0,IF((P7<"12:30"*1)*(Q7>"13:15"*1),"0:45","0: 00"))+IF(D7>="16:50"*1,"0:15","0:00") 休憩時間は12:30から13:15までの45分。 終了時刻が16:35以降なら15分の休憩時間が加算。 開始、終了が入力されていないと0:00表示。 ■当日 勤務時間 =AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6), "16:35")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"08 :00")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX (D6,"16:50")-"16:49:59","0:15")) 開始、終了が入力されていないと空白 12:30~13:15の場合は12:30、16:35以降の場合は16:35、それ以外は終 了時刻のまま。 8:00以前は8:00、12:30~13:15の場合は13:15、それ以外は開始時刻の まま。 残業していないケースをIF文を使わずに計算したいため終了時刻が 16:50以前の場合は16:50と補正して、計算結果が00分になるように細工 。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお 願いします。

その他の回答 (4)

  • 回答No.5

セルC5に =IF(A7>=TIME(13,0,0),B7-A7,IF(B7>=TIME(13,0,0),B7-A7-TIME(1,0,0),B7-A7)) の式を入れて、C7まで数式をコピーする方法もあるかな? ^^

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ん? C5に入れるにはA7とB7をA5、B5にすればいいですね?

  • 回答No.3
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

C5セルには次の式を入力して下方にオートフィルドラッグします。 =IF(COUNT(A5:B5)<>2,"",IF(AND(A5<B$2,B5>B$3),MIN(B5,A$3)-MAX(A5,A$2)-(B$3-B$2),IF(AND(A5<B$2,B5<B$3),MIN(B5,B$2)-MAX(A5,A$2),IF(AND(A5>B$2,B5>B$3),MIN(B5,A$3)-MAX(A5,B$3),"")))) C5セルからのセルの表示形式は時刻から選択します。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答、ありがとうございます。 一ずつ追っていって納得しました。

  • 回答No.2

これでは? =IF(B5<=12/24,B5-A5,IF(B5<13/24,12/24-A5,IF(A5<=12/24,B5-A5-1/24,IF(A5<13/24,B5-13/24,B5-A5))))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

お返事ありがとうございます。 分かり易いですね!  このままだと未入力のところも0:00になってしまうので =IF(COUNT($A5,$B5)<>2,"",IF~)でくくってみました。

  • 回答No.1
  • aokii
  • ベストアンサー率23% (4381/18407)

=IF(IF(AND(A5>$B$2,A5<$B$3),$B$3,IF(A5<$A$2,$A$2,IF(A5>$A$3,$A$3,A5)))>IF(AND(B5>$B$2,B5<$B$3),$B$2,IF(B5<$A$2,$A$2,IF(B5>$A$3,$A$3,B5))),0,IF(OR(IF(AND(A5>$B$2,A5<$B$3),$B$3,IF(A5<$A$2,$A$2,IF(A5>$A$3,$A$3,A5)))>=$B$3,IF(AND(B5>$B$2,B5<$B$3),$B$2,IF(B5<$A$2,$A$2,IF(B5>$A$3,$A$3,B5)))<=$B$2),IF(AND(B5>$B$2,B5<$B$3),$B$2,IF(B5<$A$2,$A$2,IF(B5>$A$3,$A$3,B5)))-IF(AND(A5>$B$2,A5<$B$3),$B$3,IF(A5<$A$2,$A$2,IF(A5>$A$3,$A$3,A5))),IF(AND(B5>$B$2,B5<$B$3),$B$2,IF(B5<$A$2,$A$2,IF(B5>$A$3,$A$3,B5)))-IF(AND(A5>$B$2,A5<$B$3),$B$3,IF(A5<$A$2,$A$2,IF(A5>$A$3,$A$3,A5)))-B3+B2))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

さっそくご回答ありがとうございます。こんなに長くなるとは思いませんでした。。。

関連するQ&A

  • 勤務時間・時間外の計算方法を教えて下さい

      A      B     C    D     E    F 出社時間 退社時間 休憩 勤務時間 時間外 勤務日数 1 8:00    17:30    1    8:00   0:30   1 2 8:00    17:00    0.5    ☆   0:00   1 4 8:00    12:30    0     4:30    ★ 5 8:00    12:00    0    4:00   0:00   0.5 6 13:00   17:00    0    4:00   0:00   0.5 (1)勤務時間を計算する場合 B1-A1-C1かB1-A1-TIME(1,0,0) で計算できることは分かったのですが、休憩時間が30分のときの計算式☆が分かりません。 (2)時間外計算は=IF(D2>TIME(8,0,0),D2-TIME(8,0,0),0)だと勤務時間が8時間の場合にしか当てはまらないので半日勤務の場合の残業計算★はどういう式になるのかも教えて頂きたいです。 出来れば、8時間のときも半日のときも一度に計算したいです。 また、勤務日数を勤務時間 8時間の場合は 1 半日の場合は 0.5 というようにすぐ表示出来るようにならないでしょうか。0.5は赤字で表示出来たらうれしいです。 無理なことを言っているかもしれませんが、お願いします。

  • エクセルで勤務時間を計算したい

    例えば、 セルAに出社時間) 9:00 セルBに退社時間) 16:00 と記入し、 2時間ごとに休憩15分あるので、 それを自動的にセルCに表示させたいです。 この場合ですと、 セルCに休憩時間) 0:45 そして、 拘束時間から、休憩時間を引いた勤務時間をセルDに表示させたいです。 セルDに勤務時間) 6:15 そして、 時給を仮に800円として、日給をセルEに表示させたいです。 *15分で200円の計算です セルEに日給) 5000円 マクロは使わないでお願いします。

  • 勤務時間の計算について

    お世話になります。 勤務時間数の計算方法について教えてください。 勤務開始時間19時で、勤務終了時間が6時のような場合の勤務時間の計算方法を教えてください。 また、勤務開始時間6時から勤務終了時間13時という場合もあるので両方に対応できるようにしたいのですいが、よろしくお願いします。 19:00-6:00=11時間 6:00-13:00=7時間

  • Excelで勤務時間表を作るにあたり

    エクセルで月の勤務時間・残業時間などを出す勤務表を作成しています。 ●通常は12:00~13:00に休憩を取る ●13:00以降に出社した場合&12:00以前に退社した場合、休憩をとらない ●12:00~13:00に出社した場合、13:00まで休憩とする 以上の条件を満たそうとして、作ってみました。 =拘束時間-IF(OR(出社>13:00,退社<13:00),0,IF(出社<=12:00,1時間の休憩,13:00-出社)) 出社のパターンは対応できたのですが、これに加え、12:00~13:00に出社した場合も、自動的に13時まで休憩を取るという計算式を作りたいのです。 どうにかして計算できないでしょうか。

  • エクセル 勤務時間から退社時間を求めたい

    教えてください。 エクセルで今勤務時間の計算をしています。 出社時間は定時8:30と決まっていますが、日によって勤務時間が8時間とか6時間とかばらばらです。 そこから退社時間をもとめたいのです。退社時間は一応お昼以降なので常に休憩時間の1時間は差し引いて表示をしたいとおもっています。つまり勤務時間8Hと入力したら、自動的に退社時間が17:30と表示される方法はありませんでしょうか。教えてください。

  • 勤務時間の計算

    申し訳ございませんが、教えて下さい。 勤務時間の計算式【=TEXT(MIN($D8,F$7)-…】と入力したのですが、全てのセルに同じ時間が 表示されています。 出退勤の時間を入力して答えが表示されるようにするにはどうすればいいでしょうか。 宜しくお願い致します。

  • エクセル 深夜勤務時間の算出方法

    こんにちは。 今、会社でエクセルを使った勤務表を作成しています。 出社時間、退社時間をそれぞれセルに入力すると、深夜勤務時間のセルに夜22:00から朝5:00の間に勤務した時間を抽出して表示したいと思っています。 例として、出社時間を「20:00」、退社時間を「3:00」と入力した場合、深夜勤務時間に「5:00」という風に計算して表示させたいのです。 深夜勤務時間にはどういった計算式をいれればいいでしょうか? よろしくお願い致します。

  • 勤務時間と勤務時間の間は8時間空けないといけない?

    勤務時間と勤務時間の間は8時間空けないといけない? 09:00-18:00 通常勤務(昼休憩1時間含む) 18:00-02:00 8時間残業 帰宅 09:00から出社 上司より、2:00帰宅~9:00出社では7時間しか間隔ない。 8時間空けないと労働基準法に違反すると言われました。 本当でしょうか?

  • Excel:勤務時間の計算がうまくいかない。

    No.1468024の質問をした者です。 勤務表を作成しています。(Excel2002を使用) 終了時刻(D列)ー開始時刻(C列)ー休憩時間(E列)=当日の勤務時間を計算しています。 No.1468024の質問でアドバイスをいただき、以下のような計算式を記述しました。 <例> =AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")) ほぼうまくいくのですが、開始時刻9:00終了時刻12:00とした場合、勤務時間4:00となり、間違ってしまいます。(正しくは勤務時間3:00。また開始時刻9:00終了時刻が12時から13時までの間の時刻で入力された場合も勤務時間3:00と出力したいです。) 休憩時間セルに以下のような計算式を書いています。 <例> =IF(COUNT(C13:D13)<2,0,IF(D13-"12:00"*1<=0,0,IF(C13-"12:00"*1>=0,0,IF(D13<"18:00"*1,"1:00","1:20")))) 休憩時間は12時から13時までの1時間。 終了時刻が17:40以降なら20分休憩時間が加算されます。 開始、終了が入力されていないと0:00表示。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお願いします。

  • 時間外勤務時間の計算方法

    1日8時間、週40時間を越える勤務時間については、割り増し賃金が必要と聞きました。 1日8時間はわかるのですが、週40時間は、いつを起算日として計算すればよいのでしょうか。 というのは、給与締め日をはさんだ場合の計算方法がわかりません。 例 日曜日を起算日 給与締め日は月末 1日7時間勤務 として 締め日が木曜日なら 日曜日から木曜日まで5日間で35時間勤務なので、この週については、当月は割り増しはなく、 翌月はこの週は金曜日の2時間分以降が割り増し、という計算でいいのでしょうか。