[[20070823095802]]　『数式を使った時間の計算』（ろんりー）　ページの最後に飛ぶ

『数式を使った時間の計算』（ろんりー）

こんにちは。出勤簿を作成しており数式を使った時間の計算ができないので
教えてください。

列にある　数式は下のとおりです。出勤時間をＧ７に退社時間をＩ７に入力しています

表しているのは残業時間（１８時３０分から２２時まで）です。
MIN($I$7,"22:00")-MAX($G$7,"18:30")　で計算しています。
これを１ヶ月分足していきたいのですが
今のままでＳＵＭ関数を使うと＃＃＃が続いて出ます。

合計を表すところの書式設定は[h]:mmです。

そのほかの１日１日のセルの書式設定は、残業時間がなければ”表示なし”ということで

h:mm;;としています。

 セル幅が狭くて表示し切れていないケースが想像されますので、セル幅を広げるとか？
 時間の計算で####となるのは計算結果がマイナスになっていることも考えられます。
 具体例を書いていただけばそちらの状況が理解しやすくなるのですが・・・(Hatch)

(Hatch)さんありがとうございます。
広げてもエラーでした。

 始業時間		終業時間		普通残業	深夜残業	定時内勤務時間

 9:00		23:50		3:30	1:50	8:00
 9:00		18:50		0:20		8:00
 9:00		18:50		0:20		8:00
 9:00		18:50		0:20		8:00
 9:00		18:50		0:20		8:00

 という感じで出しています。

 始業時間（ＧＨ列セルを結合させて表示）・終業時間（ＩＬ列セルを結合させて表示）は、データの入力規則でドロップダウンリストから選択する形です。
そこから下の１．２．３を自動的に出させます。
　
１、普通残業（セルＫ列）の出し方は、MIN($I$7,"22:00")-MAX($G$7,"18:30")です。
２、深夜残業（セルＬ列）が、MAX($I$7+($I$7<$G$7)-"22:00",0)です。
３、定時内勤務時間（セルＭ列）は、IF(OR($G7="",$I7=""),"",MIN(($I7<$G7)+$I7,Q$5)で出しています。
　
１、２、３、のセルの書式設定は先に書いた、h:mm;;です。必要なければ出力させないようにしています。

 普通残業　MIN($I$7,"22:00")-MAX($G$7,"18:30") → I7が18:30より小さいと結果が負となり時間計算ではエラーとなります。
  　　                           ^^^^  始業時間が関与するのは何故ですか。
  　　　　　　　　　　 　　　　　　　　交替勤務があるのでしょうか。
 =MAX(MIN(I7,"22:00")-"18:30",0)　←　 でよろしいような・・・

 深夜残業は　=MAX(I7,"22:00")-"22:00"+(I7<"22:00")

 定時内勤務時間は Q5　が不明なのでわかりません。　(gon-2)　
　
 h:mm;;
   ↓　　見逃してました。。。

 > 今のままでＳＵＭ関数を使うと＃＃＃が続いて出ます。
 どこに####がでますか？　
 お書きになった数式を入れても($は外しましたけど)、こちらでは再現できません(^^;)

 > I7が18:30より小さいと結果が負となり時間計算ではエラーとなります。
 こちらでは、表示形式がh:mm;;のため、エラーは表示されません。
 合計も[h]:mm;;ならエラーは表示されません。
 ただし、合計はマイナスでも計算されるのでおかしな計算になっているはずですけど。

 数式を書くならこんな感じ?
 =IF(ISERROR(TEXT(MIN(I7,"22:00")-MAX(G7,"18:30"),"h:mm")*1),0,TEXT(MIN(I7,"22:00")-MAX(G7,"18:30"),"h:mm")*1)

 深夜残業は日をまたぐのを前提(22:00以前からの継続勤務)として
 =IF(ISERROR(TEXT(I7+(I7<G7)-"22:00","h:mm")*1),0,TEXT(I7+(I7<G7)-"22:00","h:mm")*1)
 　　　(Hatch)

(Hatch)さん、ありがとうございます。

 定時内勤務時間ですが申し訳ありません、式半ばで載せてしまいましたので
 上の文に定時内勤務時間の【訂正】の式と説明をのせました。。。すみません。

 エラーが出るのは、合計時間のところでした。
 合計時間のところ以外にはエラーはでませんでした。

 直してもらった式を入れたら、普通残業と深夜残業のそれぞれ合計時間が出せました。

 定時の出し方が私が出した式のように、他のセルから反映させるのではなく
 ひとつの式にできますか？　　（ろんりー）

 > 定時内勤務時間（セルＭ列）は、IF(OR($G7="",$I7=""),"",MIN(($I7<$G7)+$I7,Q$5)で出しています。
 この式のQ5とは何ですか?　そして、この式で提示内勤務時間を求めることができるのでしょうか？
 　　(Hatch)

（ろんりー）です。
(Hatch)さんすみません。
うまくページの更新、書き込みができませんでした。

　『定時内勤務時間ですが申し訳ありません、式半ばで載せてしまいましたので

 上の文に定時内勤務時間の【訂正】の式と説明をのせました。。。』

下が、式と説明です。

定時内勤務時間の【訂正】の式

 =IF(OR($G7="",$I7=""),"",MIN(($I7<$G7)+$I7,Q$5)-MAX(Q$3,MIN($G7,Q$5))+MIN(($I7<$G7)+I7,S$5)-MAX(S$3,MIN($G7,S$5)))

 説明     就業時間は9：00～18：00です。休憩時間は12：00～13：00　と　18：30～22：00です。
　　　　　普通残業が18：30～22：00で、深夜残業時間はそれ以降です。

　　　　　下のように他の場所に、上の決まっている時間を入れて、定時内勤務時間の式に反映させまし　　　　　　た。　　　　　

    　　Ｑ　　　　　　Ｓ　　　Ｔ　　　　Ｕ
　3　 9:00	 　13:00	  18:30	  22:00
　4　12:00	　 18:00	  22:00	
　5　12:00	   18:00   22:00

↑Ｒには何も入れていません。

定時内勤務時間の式を直していただけますか。
今のままだと、合計時間がエラーになってしまいます。そのほかの所は、時間が出てくるか、空白です。

 一つの式で計算するのは面倒くさいので
9:00～12:00と13:00～18:00の2つに分けて計算したらいかがでしょう?
（セル配置を動かせないなら、どこか他の列に計算してM列で合計するとか）
そしたら、残業時間の計算と同じように出来ると思います。
　　　(Hatch)

ありがとうございます　　やってみます

すみません、もしよかったら作った式を見てください。
=(G7<"12:00","12:00"-G7,0)でまず9:00～12:00のところを作りました。

13:00～18:00も同じように作ろうと思います。

ですが、自分が作ったものでは自信がありません。正確な式にしたいのですが教えてもらえますか。。

（ろんりー）

 9:00前に出社した場合はどうなりますか？
 残業扱いなのでは？
 =min(I7,"12:00")-max(G7,"9:00")
 ではないでしょうか？

 >正確な式にしたいのですが教えてもらえますか。
 勤務表に関しては各社さまざまな基準がありますから、
 まずはそれを正しく・全ての条件をご提示いただくことが必要です。
 でないと、条件の後出しをしたがために数式がまったく違うものになってしまうことがあります。
 大変かとは思いますが、まず条件を整理してみてください。

 >休憩時間は12：00～13：00　と　18：30～22：00です。
 　　　　　　　　　　　　　　　　^^^^^^^^^^^^^18:00～18:30ではないでしょうか？

 ちなみに今わかっていることですが…
 ・ 9:00～18:00　…　定時勤務
 ・12:00～13:00,18:00～18:30(？)　…　休憩
 ・18:30～22:00　…　普通残業(9:00前出社はどうなるのでしょう？)
 ・22:00以降　　 …　深夜残業
 で間違いないですか？
 他に何か条件等ないあれば、ご提示ください。

 【疑問】
 ・深夜残業は22:00以降とありますが、何時まで深夜残業ということはないのでしょうか？
 　(よく見る事例は22:00～翌5:00までなどがありますが)
 ・定時勤務が8時間に満たなくても18:30以降は普通残業とみなされますか？
 (gloomy)

 (gloomy)さん、ありがとうございます。
ご指摘いただいた件ですが、
 ・ 9:00～18:00　…　定時勤務
 ・12:00～13:00,18:00～18:30(？)　…　休憩
 ・18:30～22:00　…　普通残業(9:00前出社はどうなるのでしょう？)
 ・22:00以降　　 …　深夜残業
 でまちがえありません。

 休憩は、直していただいた通り18:00～18:30です。
 9時前出社の場合は、出勤時間に含めていません。
 深夜残業は、22：00～5：00です。
 定時時間が、8時間に満たなくても18：30以降は普通残業とみなしています。

 他の条件は、特にありません。

 以下のような式を考えていました。　(Hatch)
 9:00～12:00
=IF(ISERROR(TEXT(MIN(I7,"12:00")-MAX(G7,"9:00"),"h:mm")),0,TEXT(MIN(I7,"12:00")-MAX(G7,"9:00"),"h:mm"))*1

 13:00～18:00
=IF(ISERROR(TEXT(MIN(I7,"18:00")-MAX(G7,"13:00"),"h:mm")),0,TEXT(MIN(I7,"18:00")-MAX(G7,"13:00"),"h:mm"))*1

(Hatch)さんありがとうございます。
私の作ったものと比べると(Hatch)さんのが立派な式で、お恥ずかしいです。

ありがとうございます。

当てはめてみます。

 遅くなりました。

     R     S     T     U     V     W    X
 1  9:00 12:00 13:00 18:00 18:30 22:00 5:00
 と時間の区切りを入力しておいてください。

 【9:00～12:00】
 =IF(G7<$S$1,IF(G7<I7,MIN(I7,$S$1)-MAX(G7,$R$1),$S$1-MAX(G7,$R$1)),0)*1
 【13:00～18:00】
 =IF(G7<I7,IF(I7>$T$1,MIN(I7,$U$1)-MAX(G7,$T$1),0),$U$1-MAX(G7,$T$1))*1
 【普通残業】
 =IF(G7<I7,IF(I7>$V$1,MIN(I7,$W$1)-$V$1,0),$W$1-MAX(G7,$V$1)+IF(I7>$X$1,MIN(I7,$R$1)-$X$1,0))
 【深夜残業】
 =IF(G7<I7,IF(I7>$W$1,I7-$W$1,0),TIME(2,0,0)+MIN(I7,$X$1))

 でどうでしょうか？

 Hatchさんへ
 >=IF(ISERROR(TEXT(MIN(I7,$U$1)-MAX(G7,$T$1),"h:mm")),0,TEXT(MIN(I7,$U$1)-MAX(G7,$T$1),"h:mm"))*1
 これだと、日付をまたぐ勤務があった場合に13:00～18:00の勤務時間が0になりませんか？
 (gloomy)

 そうですね、25:00のように入力すればいいのですけど・・・
 では前の式と組み合わせましょう・・・(Hatch)
 =IF(ISERROR(TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm")),0,
TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm"))*1

 =IF(ISERROR(TEXT(MIN(I7+(I7<G7),"18:00")-MAX(G7,"13:00"),"h:mm")),0,
TEXT(MIN(I7+(I7<G7),"18:00")-MAX(G7,"13:00"),"h:mm"))*1

 (gloomy)さん(Hatch)さんありがとうございます。

 お二人で考えてくださって感謝しています。

 少し時間がかかるかもしれませんが、理解しながら当てはめてみます。
(ろんりー)

 お世話になっております。
　
 前回教えていただいてから、出勤簿作成も随分進み、またこのサイトで勉強させていただいておりま 　す。
　
　ただ、契約社員などの自給計算の、計算方法なのですが、
  有休をとると、その日は8時間分働いたことになるのです。
　
　この場合、定時の合計時間にif関数で、”もし勤怠が有休だったら+8そう出なかったら何もしない”
　で出そうとしているのですが、式が作れなくています。

 もしよかったら教えていただきたいです。。よろしくお願いします。（ろんりー）

 合計の式に　+COUNTIF(A1:A16,"有給")*"8:00"　ということかな？　(Hatch)

 どこかのセルに「有給」を判断する項目があるのでしょうか？

 >”もし勤怠が有休だったら+8そう出なかったら何もしない”
 合計で行うのではなく、有給を取った日の「定時勤務」に"8:00"と表示して、
 合計は普通にSUM関数のみで行う方法がいいのではないでしょうか？
 でないと日付ごとの時間を足したものと合計時間が合わなくなりますよ。
 混乱を招く可能性はないでしょうか？

 有給を取った日付に"8:00"と表示する関数であれば
 =IF(有給判定のセル="有給","8:00",現在使用している定時勤務の関数)
 (gloomy)

(Hatch) さん(gloomy)さんありがとうございます。

 (Hatch) さん、ありがとうございます。教ええていただいた+COUNTIF(A1:A16,"有給")*"8:00"だと
 ほしかったものになります。そのように組めばいいのですね！！

 (gloomy)さん、おっしゃるとおり、ただ合計に足すと、
日付ごとの時間を足したものと合計時間が合わなっていしまいますね。。。

 私が浅はかでした。。。

 有休を判定するセルは、C7～C37にありますので、そちらとあわせて式を組んで見ます。

 （ろんりー）

 式を入れてしばらくして、有休を選択すると、ドキュメントが修復に入ってしまい、
 入れた式も、回復してからは消えてしまっています。

 この場合、何がもんだいなのでしょうか（汗）

　せっかく教えていただいたのに、悔しいです。　　（ろんりー）

 それはブックが破損している可能性がありますね…。

 シートを新規のブックにコピーして使用してみたらどうなりますか？
 (gloomy)

(gloomy)さん。ありがとうございました。

 こんな質問にまで親切に答えてくださって・・・(涙)

 直りました。

 大事にします。。　　　ありがとうございます。　　　（ろんりー）

 治りましたか！よかったです(^^)

 ブックの修正が入るのもさまざまな要因があるようですので、
 一番可能性が高いものを書いたんですが、合っていてホッとしました。

 出勤簿も出来たようでなによりです！
 (gloomy)

 はい!!!

 (gloomy)さん、(Hatch)さんも、

　ほんとうにありがとうございました！！！

 これからも応援しておりますので、お体にお気をつけて、この素敵なサイトを
 お守りください...（○＾＾○）

 それでは失礼いたします。　（ろんりー）→（はっぴー）

　お世話になっております。

 先日教えていただいた、普通残業時間の出し方の式ですが、→

 【そうですね、25:00のように入力すればいいのですけど・・・
 では前の式と組み合わせましょう・・・
 =IF(ISERROR(TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm")),0,
TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm"))*1

 =IF(ISERROR(TEXT(MIN(I7+(I7<G7),"18:00")-MAX(G7,"13:00"),"h:mm")),0,
TEXT(MIN(I7+(I7<G7),"18:00")-MAX(G7,"13:00"),"h:mm"))*1】

 上で(Hatch)さんが教えてくださった、
 =IF(ISERROR(TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm")),0,
TEXT(MIN(I7+(I7<G7),"12:00")-MAX(G7,"9:00"),"h:mm"))*1

 の式で、休日のときにも3：30の残業時間が表示されてしまっています。
 この場合、どの部分に『もし休日だったら何も表示させない』の式を組み込めばいいのでしょうか。。（ろんりー）

 アドバイスです。

 >もし休日だったら何も表示させない
 ということは休日であれば時間の計算をしたくないわけですね。
 つまり、計算の前に休日かどうかを判断する必要があります。
 (gloomy)

　　(gloomy)さんおはようございます。　判断箇所は、

　　A　　　B　　　　　CD　　　　　　　

 日にち	曜日	勤怠状況		遅刻	早退	始業時間		終業時間		普通残業	深夜残業	定時内勤務時間
9/1	土	休日				9:00		9:05		0:00	0:00	0:05
9/2	日					ー		ー		3:30	0:00

というように、勤怠状況のところで判断させています。

 =IF(G7<I7,IF(I7>$V$1,MIN(I7,$W$1)-$V$1,0),$W$1-MAX(G7,$V$1)+IF(I7>$X$1,MIN(I7,$R$1)-$X$1,0))で、
 =IF(c７="休日","0:00",G7<I7,IF(I7>$V$1,MIN(I7,$W$1)-$V$1,0),$W$1-MAX(G7,$V$1)+IF(I7>$X$1,MIN(I7,$R$1)-$X$1,0))
としようとしてもうまくいきません。

 前後の文から判断するに日曜日(9/2)の残業時間がおかしくなる、ということでしょうか？
 説明がありませんので土曜と日曜、どちらがうまくいかないのかわかりません。

 日曜日分と仮定して説明しますので、違うのであれば詳しくご説明をお願いします。
 (うまくいかないのはどの部分で、どこがどう上手くいかないか、出して欲しい答えはどうか等)

 >=IF(C7="休日","0:00",G7<I7,IF(I7…
      ^^^^^^^^ここではC7が"休日"のみTRUEを返します。

 例の2行目(日曜分)でいうと
 C7は空白ですので上記の『C7="休日"』という条件には当てはまらないので当然計算をしてしまいます。

 日曜の出勤は絶対にないのでしょうか？
 であれば、最初のIFの条件を『曜日が日曜、もしくは勤怠状況が休日であれば』と変更。
 日曜の出勤もあるのであれば勤怠状況に休日と記入させるしかありません。
 (gloomy)

 =IF(C7="休日","0:00",IF(G7<I7,IF(I7… …,0))) ？
                      ~~~                    ~
 （dack）

　(gloomy)さん、

 >日曜の出勤は絶対にないのでしょうか？
    日曜の出勤はあります。そのときの勤怠状況の選択方法は、”休日出勤”です。

 今の問題点は
 例えば、9/1（土）、9/2（日）ともに、勤怠状況を”休日”とさせて、
『始業時間』『終業時間』の時間を入力させなくても、必ず3：30の残業時間がついてしまうことです。
　　　　　　　　　　　　　　　↓
 （出勤していなくて、時間を選択しないときは”-”を選びます。）

 その式は、今は、
 =IF(ISERROR(TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1),0,TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1)
　です。

 先ほども説明しましたが、上の式だと、休日で時間を選択しなくても『3：30』の残業がついてしまうのです。

 その解決策として、自分なりに　=IF(C7="休日","0：00",ISERROR(TEXT(MIN(I8,"22:00")-MAX　 (G8,"18:30"),"h:mm")*1),0,TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1)

 としてみたのですが、エラーになります。『この関数に関して多すぎる因数が使われています』となります。

 (gloomy)さんにご指摘いただいた、下の件ですが
 >例の2行目(日曜分)でいうと
 C7は空白ですので上記の『C7="休日"』という条件には当てはまらないので当然計算をしてしまいます。

 →9/2(日)に、”休日”を入れてみてもエラーでした。

 ですので、休日で、時間を選択しない場合は（”-”と表示）、普通残業の箇所が、『0：00』とさせないと正しい残業時間が計算できないので、その方法を探しています。

 勉強不足で申し訳ありませんが、よろしくお願いいたします。（ろんりー）

 私も見落としていましたが、dackさんのコメントはご覧になりましたか？

 >その解決策として、自分なりに　=IF(C7="休日","0：00",ISERROR(TEXT(MIN(I8,"22:00")-MAX　 (G8,"18:30"),"h:mm")*1),0,TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1)
 >としてみたのですが、エラーになります。『この関数に関して多すぎる因数が使われています』となります。

 こちらも同じですね。
 IF関数は　IF(条件式,真の場合,偽の場合)という構成です。
 ここでろんりーさんが使われているIF関数を前から見ていくと…
 条件式　　--->　C7="休日"
 真の場合　--->　"0:00"
 偽の場合　--->　ISERROR(TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1)

 となり、以降の 『,0,TEXT(MIN(I8,"22:00")-MAX(G8,"18:30"),"h:mm")*1)』が残ってしまいます。
 なのでExcelから『この関数に関して多すぎる因数が使われています』といわれてしまっているのです。

 おそらくISERRORの前に 『IF(』 関数の最後に『)』 が抜けているのではないかと思います。
 (gloomy)

 (gloomy)さんありがとうございます。
足りない文字を入れましたら、解決しました。

コメント返信：

[ 一覧(最新更新順) ]

[[20070823095802]] 『数式を使った時間の計算』（ろんりー） ページの最後に飛ぶ

[[20070823095802]]　『数式を使った時間の計算』（ろんりー）　ページの最後に飛ぶ