[[20071030144554]]　『時間の計算』（さくら）　ページの最後に飛ぶ

『時間の計算』（さくら）

 宜しくお願いいたします。
 時間の計算をするときに

       efg        hij       klm           nop            prst
   4 出勤時間　　退社時間　　午前　　　　　午後	労働時間	　　　
   5  9：00　　　15：00　　2時間00分	　2時間00分　　　４時間00分

 というフォームで、労働時間を、午前と午後に分けて出そうとしています。
 いま、どの表示方法も、時間と分でセルを分けて表示させています。

 そのときの、午前時間の出し方（時間：分）、午後時間（時間：分）の、計算式の作り方、時間の出し方を教えてください。

 自分で、午前の時間：="12"-E5
         午前の分　：=G5
         午後の時間：=IF(H5<="12",0,H5-"12")で出してみましたが、
 休憩の時間をはさんだり、分の繰越があると、どんな式を組んだらいいのかわかりません。

 休憩：12：00～13：00　　18：00～18：30で、
 規定で決まっている就業時間は、9：00～18：00の８時間労働です。

　とても急いでいます。宜しくお願いします。

 お急ぎのところ恐縮ですが、、

 >どの表示方法も、時間と分でセルを分けて表示させています。
 >分の繰越があると、どんな式を組んだらいいのかわかりません。
 　　　↑
 こう云うことがあるので、計算に使うのであれば「時間と分を別々のセルに分ける」のは好ましくありません。

 普通に「9:00」と云う風に、1つのセルに入力する方式へ変更出来ないのでしょうか？

 (半平太)

 (半平太)さんありがとうございます
 入力のことを考えると、時間と分は分けて表示させたいです。。。

                                                       (さくら)

 なんどもすみません。

　見ていただきたいのは、午後の”分”の計算方法なので、お願いします。

 自分でくんでみました。

 ｛もし、退社時間の「時間」が”13”より”小さかったら”00”、そうでなかったら退社時間の「分」を表示”｝という、式を組みました。

　↓ 下です。

 =IF(H5<"13","00",J5)

        efg         hij        klm            nop         prst
   4 出勤時間　　退社時間　　午前　　　　　午後	労働時間	　　　
   5  9：00　　　15：00　　2時間00分	　2時間00分　　　４時間00分

 しかし、退社時間を、”11”にしても”14”にしても、表示されるのは「00」になってしまいます。

 式自体が問題なのは分かるのですが、どうしていいのか分かりません。

 説明不足でしょうか。　　　　　　（さくら）

 午前は3時間ですよね？
 取り敢えず、「0時間」も表示されますが・・・・

 午前（時間）K5
=TEXT(MIN(TIME(H5,J5,),TIME(12,0,))-MIN(TIME(E5,G5,),TIME(12,0,)),"h")
 午前（分）  M5
=SUBSTITUTE(TEXT(MIN(TIME(H5,J5,),TIME(12,0,))-MIN(TIME(E5,G5,),TIME(12,0,)),"h:mm"),K5&":",)
 午後（時間）N5
=TEXT((MIN(TIME(H5,J5,),TIME(18,0,))-MAX(TIME(E5,G5,),TIME(13,0,)))+(TIME(H5,J5,)-MIN(TIME(H5,J5,),TIME(18,30,))),"h")
 午後（分）  P5
=SUBSTITUTE(TEXT((MIN(TIME(H5,J5,),TIME(18,0,))-MAX(TIME(E5,G5,),TIME(13,0,)))+(TIME(H5,J5,)-MIN(TIME(H5,J5,),TIME(18,30,))),"h:mm"),N5&":",)
 労務時間（時間）Q5
=TEXT(TIME(K5,M5,)+TIME(N5,P5,),"h")
 労務時間（分）  S5
=SUBSTITUTE(TEXT(TIME(K5,M5,)+TIME(N5,P5,),"h:mm"),Q5&":",)

 上手く行くと良いのですが。

 (HANA)

 > =IF(H5<"13","00",J5)
 　　　　　↑
 　　　　これは文字ですので　 =IF(H5<"13"*1,"00",J5)　としてみてください。
　　　　　　　　　　　　　　　  　　　　↑　
 　　　　　　　　　　　　　　　　　ナンセンスでした。=IF(H5<13,"00",J5)　としてみてください、でした。

 以下は私案です。

 ※　8分や、0分は、8や0となります。外見を08や00にしたい場合は、セルの書式(表示形式)を"00"として下さい。

 (1) K5セル =HOUR(0.5-MIN((E5+G5/60)/24,0.5))
 (2) M5セル =MINUTE(0.5-MIN((E5+G5/60)/24,0.5))
 (3) N5セル =HOUR(MAX(0,MIN("18:00",(H5+J5/60)/24)-MAX("13:00",(E5+G5/60)/24)))
 (4) P5セル =MINUTE(MAX(0,MIN("18:00",(H5+J5/60)/24)-MAX("13:00",(E5+G5/60)/24)))
 (5) Q5セル =HOUR((K5+N5+(M5+P5)/60)/24)
 (6) S5セル =MINUTE((K5+N5+(M5+P5)/60)/24)

  行  __ E ___  F  _G_ ___H____ _I_ _J_ __K___ _L_ _M_ _N__ _O_ _P_ ___Q____ _R_ _S_
   4  出勤時間         退社時間         午前中         午後         労働時間        
   5         9 :     0       15 :     0      3 :     0    2 :     0        5 :     0
   6        11 :    45       20 :     0      0 :    15    6 :    30        6 :    45

  (半平太)

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

 入力してみました。
 どうしても午前に退社した場合に、午後時間がエラーになってしまいました。

 (半平太)さんありがとうございます。
 完璧でした。

 さすがです（_ _）非常に驚きました!!

 もうひとつ質問させてください。

 残業時間、深夜残業時間はどのように出したらいいでしょうか。

 深夜残業は、自分で作ると、単純なものしかできません。。

 > 残業時間、深夜残業時間はどのように出したらいいでしょうか。
 雰囲気的に、深夜残業は無い職場かなと思っていたのですが、やはりあるのですねぇ。

 残業は18:30以降の実働時間だと思いますが(深夜残業除きですか、含みですか?)、
 深夜は22:00以降ですか？

 深夜は、24:00を超えることもありますか？
 　　超える場合、どのように入力しているのですか？　
 　　　 1:15　ですか？
 　　　25:15  ですか？

 (半平太)

（半平太）様

 おつかれさまです。
 ありがとうございます。

 はい。時間は残業は18：30以降の実労働時間で、深夜残業は22：00以降です。
 深夜は22：00を越えることもあります。

 超えたときの入力方法は25：15で出しています。
 その方が、計算しやすいかと思いまして。。。　　
 宜しくお願いいたします。　　　　　　　　　（さくら）

 (1)  K5セル =IF(COUNT(E5:J5)<4,"",HOUR(0.5-MIN((E5+G5/60)/24,0.5)))
 (2)  L5セル =IF(K5="","",":")
 (3)  M5セル =IF(K5="","",MINUTE(0.5-MIN((E5+G5/60)/24,0.5)))

 (4)  N5セル =IF(K5="","",HOUR(MAX(0,MIN("18:00",(H5+J5/60)/24)-MAX("13:00",(E5+G5/60)/24))))
 (5)  O5セル =IF(K5="","",":")
 (6)  P5セル =IF(K5="","",MINUTE(MAX(0,MIN("18:00",(H5+J5/60)/24)-MAX("13:00",(E5+G5/60)/24))))

 (7)  Q5セル =IF(K5="","",HOUR(MAX(0,MIN("22:00",(H5+J5/60)/24)-MAX("18:30",(E5+G5/60)/24))))
 (8)  R5セル =IF(K5="","",":")
 (9)  S5セル =IF(K5="","",MINUTE(MAX(0,MIN("22:00",(H5+J5/60)/24)-MAX("18:30",(E5+G5/60)/24))))

 (10) T5セル =IF(K5="","",HOUR(MAX(0,(H5+J5/60)/24-MAX((E5+G5/60)/24,"22:00"))))
 (11) U5セル =IF(K5="","",":")
 (12) V5セル =IF(K5="","",MINUTE(MAX(0,(H5+J5/60)/24-MAX((E5+G5/60)/24,"22:00"))))

 (13) W5セル =IF(K5="","",HOUR((K5+N5+Q5+T5+(M5+P5+S5+V5)/60)/24))
 (14) X5セル =IF(K5="","",":")
 (15) Y5セル =IF(K5="","",MINUTE((K5+N5+Q5+T5+(M5+P5+S5+V5)/60)/24))

  行   E   F  _G_ _H_ _I_ _J_ _K_ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ _S_ _T_ _U_ _V_ _W_ _X_ _Y_
   4  出勤時間     退社時間    午前中      午後        残業時間    深夜残業    労働時間計  
   5   09 :    00  15 :    00  03 :    00  02 :    00  00 :    00  00 :    00  05 :    00
   6   11 :    45  20 :    00  00 :    15  05 :    00  01 :    30  00 :    00  06 :    45
   7   11 :    45  25 :    15  00 :    15  05 :    00  03 :    30  03 :    15  12 :    00
   8

 (半平太)

 (半平太) さんありがとうございます。

　すみません、遅くなりました。

　教えていただいたとおりに、式を入力しましたら見違えるようなものになりまして、またかなり驚きました。。。大変助かりました。
　ありがとうございます。

　１点だけ質問と、見ていただきたい式があります。

　午前中の　K5セル =IF(COUNT(E5:J5)<4,"",HOUR(0.5-MIN((E5+G5/60)/24,0.5)))に関してですが
　出勤時間を、８時（９時前）にしても、９時からのカウント（最長３時間）にしたいのですが、
　どのように直したらよいのかわかりません。

  おしえていただけますか？

　教えてていただいた時間から、遅刻時間・早退時間を出してみました。

  z5（遅刻時間の時間）=IF(E5>"9"*1,E5-"9"*1,"00") ?
 AA5                  =IF(K5="","",":")   
 AB5 (遅刻時間の分)　
 AC5（早退時間の時間）=IF(H5<"18"*1,"18"*1-H5,"00") ?
 AD5　　　　　　　　　=IF(K5="","",":")
 AE5（早退時間の分）

 ですが、分が出せず、なおかつ、時間のほうもこれだとよくないはずなのです。
 すみません、教えていただいた式を参考につくれればいいのですが、単純な式しか分からないので理解にかなりじかんがかかりそうです（涙）
 よろしくご指導願います。　　　（さくら）

 すみません、重ねての質問の上、ここまでお聞きしていいのかわからないのですが、

 定時労働時間（9：00から18：00までの間の実働時間）を出しました。（下）
 定時労働時間（時間）　=IF($K$5="","",HOUR(($K$5+$N$5+($M$5+$P$5)/60)/24))
 定時労働時間（分　）　=IF($K$5="","",MINUTE(($K$5+$N$5+($M$5+$P$5)/60)/24))

 知りたいのは、定時労働時間と遅刻時間（まだ出せていない）と残業時間の関係です。

 残業をしても、定時労働時間が８時間以内だったら、残業は18：30からのカウントではなく、
 定時労働時間が８時間を越えた時間からカウントというのを出したいのです。。。。。

 例えば、10時に出勤して（1時間の遅刻）、20時に退社した場合に、18：30から残業をカウントするのではなく
 8時間を過ぎた、19時からカウントしたいのですが、こんなことってできますでしょうか。。。。。

 Ｑ5～Ｓ5で出していただいた、残業時間とは別のセルに計算させたいです。

 ご協力お願いします。
　
   行   E   F  _G_ _H_ _I_ _J_ _K_ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ _S_ _T_ _U_ _V_ _W_ _X_ _Y_
   4  出勤時間     退社時間    午前中      午後        残業時間    深夜残業    労働時間計  
   5   09 :    00  15 :    00  03 :    00  02 :    00  00 :    00  00 :    00  05 :    00
   6   11 :    45  20 :    00  00 :    15  05 :    00  01 :    30  00 :    00  06 :    45
   7   11 :    45  25 :    15  00 :    15  05 :    00  03 :    30  03 :    15  12 :    00
   8     
（さくら）

 >遅刻時間・早退時間を出してみました。 
 >Z5（遅刻時間の時間）=IF(E5>"9"*1,E5-"9"*1,"00") ?
  私の以前の「間違った回答」を忠実に実行されているのを拝見して、申し訳なく思います。

  以下の様にするのが正しいです。
  （誤)　　　　 　　　　　　    　　　(正) ただし、E5セルには数値が入っているものとします.
  　　　　　　　    　　　　　　  　　 ↓
  =IF(E5>"9"*1,E5-"9"*1,"00") → =IF(E5>9,E5-9,0)

 >遅刻/早退時間の正しい算出方法ですが、算出する目的が追加質問の通りだとすると、
 「定時労働時間」も「遅刻/早退時間」も出す必要はないと思います。以下の算式で求まらないでしょうか？

 定時労働後深夜 ＝「深夜残業」と「総実働時間 - 8時間(計算結果がマイナスならゼロ)」のどちらか少ない方の時間
 定時労働後残業 ＝「残業時間」と「総実働時間 - 8時間 - 定時労働後深夜(計算結果がマイナスならゼロ)」の少ない方の時間

 (1) AC5セル =IF(K5="","",HOUR(MIN(MAX(0,W5+Y5/60-8-AF5-AH5/60),Q5+S5/60)/24))
 (2) AD5セル =IF(K5="","",":")
 (3) AE5セル =IF(K5="","",MINUTE(MIN(MAX(0,W5+Y5/60-8-AF5-AH5/60),Q5+S5/60)/24))

 (4) AF5セル =IF(K5="","",HOUR(MIN(MAX(0,W5+Y5/60-8),T5+V5/60)/24))
 (5) AG5セル =IF(K5="","",":")
 (6) AH5セル =IF(K5="","",MINUTE(MIN(MAX(0,W5+Y5/60-8),T5+V5/60)/24))

  行   E F G   H_I_J_  _K_ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ _S_ _T_ _U_ _V_ _W_ _X_ _Y Z AA AB AC_ _AD_ _AE_ 　_AF_ _AG_ _AH_
   4   出勤 　 退社    午前中      午後        残業時間    深夜残業    総実働時間         定労考慮後残業  定労考慮後深夜
   5   09:00   15 :00   3 :    00   2 :    00   0 :    00   0 :    00   5 :    00          0:00            0:00
   6   11:45   20 :00   0 :    15   5 :    00   1 :    30   0 :    00   6 :    45          0:00            0:00
   7   11:45   25 :15   0 :    15   5 :    00   3 :    30   3 :    15  12 :    00          0:45            3:15
   8   10:00   20 :00   2 :    00   5 :    00   1 :    30   0 :    00   8 :    30          0:30            0:00
   9   10:30   23 :00   1 :    30   5 :    00   3 :    30   1 :    00  11 :    00          2:00            1:00

   (半平太)

 >午前中の　K5セル =IF(COUNT(E5:J5)<4,"",HOUR(0.5-MIN((E5+G5/60)/24,0.5)))に関してですが　
 >出勤時間を、８時（９時前）にしても、９時からのカウント（最長３時間）にしたいのですが 
 これを忘れていました。

 (1) K5セル =IF(COUNT(E5:J5)<4,"",HOUR(MAX(0,MIN(0.5,(H5+J5/60)/24)-MAX("9:00",(E5+G5/60)/24))))
 (2) L5セル =IF(K5="","",":")
 (3) M5セル =IF(K5="","",MINUTE(MAX(0,MIN(0.5,(H5+J5/60)/24)-MAX("9:00",(E5+G5/60)/24))))
                                           ↑

 ※「0.5」は、"12:00"と同じ意味です。　つまり 1="24:00"　→ 0.5(半日)="12:00" ですから。

 (半平太)

 (半平太)さん、夜中だというのに、式を練ってくださってありがとうございます。

 ほしかった、「午前中の時間」がおかげさまで出ました!!!!!!!
 自分でやったらきっと一ヶ月はかかっていました..（_ _）..

 ＞さて、遅刻/早退時間の正しい算出方法ですが、算出する目的が追加質問の通りだとすると、
 「定時労働時間」も「遅刻/早退時間」も出す必要ない

 そうですね、、確かにそうですね。

 しかし、どうしても、その日の遅刻時間・早退時間を目で見て把握したいです。

 行   E F G   H_I_J_  _K_ _L_ _M_ _N_ _O_ _P_ _Q_ _R_ _S_ _T_ _U_ _V_ _W_ _X_ _Y Z AA AB AC_ _AD_ _AE_ 　_AF_ _AG_ _AH_
   4   出勤 　 退社    午前中      午後        残業時間    深夜残業    総実働時間         定労考慮後残業  定労考慮後深夜
   5   09:00   15 :00   3 :    00   2 :    00   0 :    00   0 :    00   5 :    00          0:00            0:00
   6   11:45   20 :00   0 :    15   5 :    00   1 :    30   0 :    00   6 :    45          0:00            0:00
   7   11:45   25 :15   0 :    15   5 :    00   3 :    30   3 :    15  12 :    00          0:45            3:15
   8   10:00   20 :00   2 :    00   5 :    00   1 :    30   0 :    00   8 :    30          0:30            0:00
   9   10:30   23 :00   1 :    30   5 :    00   3 :    30   1 :    00  11 :    00          2:00            1:00

　の、AHの隣のところから、出そうと思います。

 式は、先ほど直してもらったものを参考に、直して、作ってみました。
 申し訳ないのですが、みてください。

 （遅刻時間の時間）=IF(K5="","",E5>9,E5-9)
               :    =IF(K5="","",":")   
  (遅刻時間の分)　?
 （早退時間の時間）=IF(K5="","",H5<18,18-H5) 
 　　　　　　　:　　=IF(K5="","",":")
 （早退時間の分） ?

 >しかし、どうしても、その日の遅刻時間・早退時間を目で見て把握したいです。

 　　　　仮に「分」の問題がないとしても、この式は、午後出勤の人は、昼休時間が含まれてしまいます。
 　　　　　　　　　　　　　　　　　　　　　↓
 > （遅刻時間の時間）=IF(K5="","",if(E5>9,E5-9,0))

 基本的には、E5+G5/60 -9 (分も考慮)でいいのですが、昼食休憩(*)を引く必要があると思います。

 ※　昼みの時間は　「E5+G5/60 - 12」(ただし計算結果がマイナスなら0、1時間以上なら1まで)となります。

 (1) AI5セル =IF(K5="","",HOUR(MAX(0,(E5+G5/60-9-MIN(1,MAX(0,(E5+G5/60-12))))/24)))
 (2) AK5セル =IF(K5="","",MINUTE(MAX(0,(E5+G5/60-9-MIN(1,MAX(0,(E5+G5/60-12))))/24)))

 同様に早退は、

 (3) AL5セル =IF(K5="","",HOUR(MAX(0,(18-H5-J5/60-MIN(1,MAX(0,(13-H5-J5/60))))/24)))
 (4) AN5セル =IF(K5="","",MINUTE(MAX(0,(18-H5-J5/60-MIN(1,MAX(0,(13-H5-J5/60))))/24)))

  (半平太)

　(半平太)さん、ありがとうございました。

 おしえていただいた通りになおしました。全て、したいことが可能になりました。
 ありがとうございました。

 ただ、この時間の集計で、時間・分と、ＳＵＭ関数で、出そうとしました。

 分の繰越についてですが、どのようにしたらいいでしょうか。。

 １．「時間」はその合計に、「分」の合計を60で割った整数部分を加えたものです。

 　　「時間」= 時間データの合計　+　int ( 分の合計 / 60 )

 ２．「分」は、分データの合計を60で割った余りの部分です。

 　　「分」= MOD ( 分の合計 , 60 )

 (半平太)

コメント返信：

[ 一覧(最新更新順) ]

[[20071030144554]] 『時間の計算』（さくら） ページの最後に飛ぶ

[[20071030144554]]　『時間の計算』（さくら）　ページの最後に飛ぶ