[[20170804174210]]　『時間管理』（ひら）　ページの最後に飛ぶ

『時間管理』（ひら）

すみません。
数式で出来るのかわかりませんが、可能であれば教えて頂けますでしょうか。

⚫<基本時間振り分け>
定時→AM9:00～PM17:30
休憩→PM12:00～13:00
残業→PM18:00～PM22:00
深夜残業→PM22:00～AM5:00
早出→AM5:00～AM8:00

上記⚫の時間振り分けに基づき、下記エクセルの表で「出勤」と「退勤」の時間を入力したらその範囲内の該当時間の項目に「1（1時間）」など入るようにすることは可能でしょうか。分は0.5（30分）単位で表記します。

<例>
（A1)「出勤」→AM7:10
（B1)「退勤」→AM24:00
（C1)「休憩」→1
（D1)「定時」→7.5
（E1)「早出」→1
（F1)「残業」→4
（G1)「深夜残業」→2

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

例の定時7.5はは休憩1を引いたもので表示してます。
（ひら） 2017/08/04(金) 17:53

 ＞（A1)「出勤」→AM7:10 
 ＞（B1)「退勤」→AM24:00
 ＞（E1)「早出」→1

 １．「出勤」→AM7:30　の時、早出は？
 ２．「出勤」→AM7:45　の時、早出は？

 ３．5:00より早く出勤させられる事は無いのですか？

（半平太） 2017/08/04(金) 19:18

1.早出、残業、深夜残業は一時間単位なので「1」です。
2.1と同様です。
3.AM5:00～早出となり、それより早いのは前日の深夜残業扱いになります。
（ひら） 2017/08/05(土) 09:16

 ＞3.AM5:00～早出となり、それより早いのは前日の深夜残業扱いになります。

 「前日の深夜」ですか？！

  この種の質問に10年間回答していますが、初めてです。

 お互いに勘違いが無いか、再確認させてください。

 <今回のルール>
  行  __A__  ____B____  __C__  __D__  __E__  __F__  __G__
   1  出勤   退勤       休憩   定時   早出   残業   深夜 
   2  7:10   17:30      1      7.5      1      0      3 　　←「翌日の深夜」を前日にアカウント
   3  2:00   14:00      1      4        3      0      0

 <これまでの経験>
  行  __A__  ____B____  __C__  __D__  __E__  __F__  __G__
   1  出勤   退勤       休憩   定時   早出   残業   深夜 
   2  7:10   17:30      1      7.5      1      0      0 
   3  2:00   14:00      1      4        3      0      3 　  ←　当日に「深夜」をカウント

（半平太） 2017/08/05(土) 09:58

ご指摘ありがとうございます。
申し訳ございません。
間違えておりました。当日の深夜になります。
なので「これまでの経験」の方が正です。
（ひら） 2017/08/05(土) 10:18

 小数演算誤差対策が不充分のため、自己削除。

（半平太） 2017/08/05(土) 20:54

ありがとうございます。
教えて頂いた数式を今日試してみようと思ってました。

（ひら） 2017/08/06(日) 08:48

 よくよく考えてみると、「休憩」には意味がないような気がしてきましたので省略します。

 ※意味が無い項目について、全体との整合性を考慮しながら
 　繰上げ条件をあれこれ考えるのは徒労に感じました。

 　もし必要なのでしたら、出勤時刻や退勤時刻が12:00～13:00の間になった場合に
 　どんな時間になればいいのか、追加説明をお願いします。

 (1) D2セル =IF(ISNUMBER($B2),ROUNDUP(ROUND((MAX(0,MIN("12:00",$B2+($B2<$A2))-MAX("9:00",$A2))+MAX(0,MIN("17:30",$B2+($B2<$A2))-MAX("13:00",$A2)))*1440,0)/30,0)*0.5,"")
 (2) E2セル =IF($D2="","",ROUNDUP(ROUND(MAX(0,MIN("8:00",$B2+($B2<$A2))-MAX("5:00",$A2))*1440,0)/60,0))
 (3) F2セル =IF($D2="","",ROUNDUP(ROUND((MAX(0,MIN("22:00",$B2+($B2<$A2))-MAX("18:00",$A2))+MAX(0,$B2+($B2<$A2)-MAX("29:00",$A2)))*1440,0)/60,0))
 (4) G2セル =IF($D2="","",ROUNDUP(ROUND((MAX(0,MIN("5:00",$B2+($B2<$A2))-$A2)+MAX(0,MIN("29:00",$B2+($B2<$A2))-MAX("22:00",$A2)))*1440,0)/60,0))

 <結果図>
  行  __A__  __B__  __C__  __D__  __E__  __F__  __G__
   1  出勤   退勤   休憩   定時   早出   残業   深夜 
   2  7:45   17:30          7.5      1      0      0 
   3  2:00   14:00          4　      3      0      3 
   4  3:00    1:00          7.5      3      4      5

（半平太） 2017/08/06(日) 09:43

確認が遅くなりすみません。
ありがとうございます！さすがですね。
ご依頼の通りになっていました。すごいです。
休憩はその時間帯に出勤も無いでしょうし、これで大丈夫です。

ただ大変申し訳ございませんが、変更と追加が発生しました。
自分でも頂いた数式を元に考えてみましたが、
初歩的なこともわからないので全く駄目で情けないです。
以下、ご対応可能でしょうか。

「変更点」
⚫<残業>最初の1時間以降は0.5（30分）単位
つまり～18:30だけ１時間、18:30～は0.5（30分）
⚫<深夜>全て0.5（30分）単位

「追加」
追加は「遅刻」「早退」の枠をもうけたいのですが、
遅刻・早退は0.5(30分単位)です。

 <結果図>がうまく表示されないので以下が例です。
出勤9:15   退勤17:35   定時7   遅刻0.5 
出勤8:50   退勤16:10   定時6   早退1.5
（ひら） 2017/08/07(月) 12:30

 ＞「追加」 
 ＞追加は「遅刻」「早退」の枠をもうけたいのですが、 
 ＞遅刻・早退は0.5(30分単位)です。

 うーん、いままでは労働者側に有利な切り上げだったんですけど、
 今回は、不利な方向に丸めるんですよね？
 (1分でも遅刻すれば、30分の遅刻でしょ？）

 なんか横やりが入る懸念があるんですが・・

（半平太） 2017/08/07(月) 17:22

そうですね。
不利ですが新しいルールでは無いんで大丈夫と思います。
今まで紙で管理していたものをExcel管理することになったものですから。
早退、遅刻の枠が無いと定時も狂うので必要となりまして。
後から申し訳ございません。
（ひら） 2017/08/07(月) 17:57

 私自身は、サービス残業日常茶飯事の時代に生きたので、こんな事に首をつっこむ気はないです。
 今でも実態がどんだけ変わっているものなのか？
 ただ、そうは言っても、一日単位で数式がそう算出すべく作ってあれば明白な違反の証しですけどね。

 (1) D2セル =IF($E2="","",7.5-H2-I2)
 (2) E2セル =IF(ISNUMBER($B2),ROUNDUP(ROUND(MAX(0,MIN("8:00",$B2+($B2<$A2))-MAX("5:00",$A2))*1440,0)/60,0),"")
 (3) F2セル =IF($E2="","",(ROUNDUP(ROUND((MAX(0,MIN("22:00",$B2+($B2<$A2))-MAX("18:00",$A2))+MAX(0,$B2+($B2<$A2)-MAX("29:00",$A2)))*1440,0)/30,0)+AND("18:00"-$B2<0,$B2-"18:30"<=0))*0.5)
 (4) G2セル =IF($E2="","",ROUNDUP(ROUND((MAX(0,MIN("5:00",$B2+($B2<$A2))-$A2)+MAX(0,MIN("29:00",$B2+($B2<$A2))-MAX("22:00",$A2)))*1440,0)/30,0)*0.5)
 (5) H2セル =IF($E2="","",ROUNDUP(ROUND((MAX(0,MIN("12:00",$A2)-"9:00")+MAX(0,MIN("17:30",$A2)-"13:00"))*1440,0)/30,0)*0.5)
 (6) I2セル =IF($E2="","",ROUNDUP(ROUND((MAX(0,"12:00"-MAX("9:00",$B2+($B2<$A2)))+MAX(0,"17:30"-MAX("13:00",$B2+($B2<$A2))))*1440,0)/30,0)*0.5)

 <結果図>
  行  __A__  __B__  __C__  __D__  __E__  __F__  __G__  __H__  __I__
   1  出勤   退勤   休憩   定時   早出   残業   深夜   遅刻   早退 
   2  7:45   19:01          7.5      1    1.5    0      0      0 
   3  2:00   14:00          4        3    0      3      0      3.5 
   4  3:00   24:10          7.5      3    4      4.5    0      0 
   5  3:00    1:10          7.5      3    4      5.5    0      0 
   6  9:15   17:35          7        0    0      0      0.5    0 
   7  8:50   16:10          6        0    0      0      0      1.5

（半平太） 2017/08/07(月) 19:08

度々申し訳ございません。
今、Excelが使用出来ない状態なので明日確認させて頂きます。

初心者すぎてお恥ずかしいのですが、
出勤、退勤の入力セルの形式は何に設定されてますでしょうか。
こちら24:00が0:00に表示されています。
出来れば24:00と表示したいのですが。

あと仮に8月1日の23:00から8月2日の23:30まで勤務した場合、
出勤、退勤に入力したら結果は正確に表示されますでしょうか。
それとも8月1日の23:00～24:00までの結果を先に出し、
8月2日の24:00～23:30までの結果を出し、合計するという方法でしょうか。

（ひら） 2017/08/07(月) 21:15

 ＞出勤、退勤の入力セルの形式は何に設定されてますでしょうか。

 [h]:mm;@
 ↑
 カギ括弧で囲う必要あり。

 ＞あと仮に8月1日の23:00から8月2日の23:30まで勤務した場合、 
 ＞出勤、退勤に入力したら結果は正確に表示されますでしょうか。

 １．24時間以上の勤務は想定しておりませんので計算できません。
 　　実際にそんなケースがあるんでしょうか？

 　　時刻データはどう入れるのですか？
 　　以下の2ケースをどう区別するのか、教えてください。

 　　(1)8月1日の23:00から8月1日の23:30まで勤務した場合
 　　(2)8月1日の23:00から8月2日の23:30まで勤務した場合

 ２．ついでですけど、23:00から翌15:00まで勤務した場合、
 　　定時はどうなるんですか？

（半平太） 2017/08/07(月) 22:14

半平太様

確認させて頂きました！
形式設定もうまく出来ました。
ありがとうございます。

説明不足ですみません。
以下、修正可能でしょうか。

<残業>
～18:00までで1時間でOKですが19:00ジャストも1時間のままでした。
19:00で1.5になるように出来ますでしょうか。
なので18:59までは1となります。
以降も19:30→2、20:00→2.5
と同じように続きます。最終22:00では4.5となります。

ご質問頂いた件ですが、
1.そうなんです。区別は出来ないですね。
一年に1,2回はこのような勤務になる人が決まった人ですがいるようです。
前回は手書きでタイムカードに記入していました。
その後、基本時間に基づいて計算するようです。

2.翌9:00～15:00て定時時間は5時間ですね。

（ひら） 2017/08/08(火) 13:10

 仕様が固まり切っていない印象を受けますので、私はここでドロップアウトします。　m(__)m

（半平太） 2017/08/08(火) 13:42

お手数をおかけし、申し訳ございませんでした。
そしてありがとうございました。
大変助かりました。

ただあと「残業」の件だけクリア出来れば完成だったのでとても残念です。

すみません、今さらですが上記「～18:00までで1時間でOKですが」
という所を誤植しておりました。
「～18:30までは」でした。
（ひら） 2017/08/08(火) 15:37

コメント返信：

[ 一覧(最新更新順) ]

[[20170804174210]] 『時間管理』（ひら） ページの最後に飛ぶ

[[20170804174210]]　『時間管理』（ひら）　ページの最後に飛ぶ