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分になるように細工
。
言葉足らずでしたら、進んで補足させていただきますので、よろしくお
願いします。
No.2ベストアンサー
- 回答日時:
休憩時間を原則45分として計算したものを<労働時間>、
<労働時間>が8時間以上の場合にそこから15分を差し引いたもの(8時間未満の場合にはそのまま)を<勤務時間>とする(つまり、<勤務時間>は<労働時間>の関数である)、
というふうに理解していいですか?
それでいいなら、勤務時間フィールドと労働時間フィールドとを分けることをおすすめします。
ダイレクトに勤務時間を求めるとなると、式が異常に長くなるからです。
以下、修正を必要最小限にとどめたやり方をご紹介します。
■当日勤務時間→労働時間
「+FLOOR(MAX(D6,"16:50")-"16:49:59","0:15")」の部分を
「+FLOOR(MAX(D6,"16:35")-"16:34:59","0:15")」に変更する。
(「勤務時間」ではなく)「労働時間」を求める式になります。
■勤務時間(新たに列を作る)
労働時間を求める式が入ったセルを「●」とすると、
「=IF(●>="8:00"*1,●-"0:15",●)」。
※●の部分に労働時間を求める式を入れると、ダイレクトに勤務時間を求める式になります。
■休憩時間
後半「+IF(D7>="16:50"*1,"0:15","0:00")」の部分を
「+IF(●>="8:00"*1,"0:15","0:00")」に変更。
この回答への補足
試した結果を報告します。
以下のように計算式を設定しました。
■8時-16時35分(定時)の場合
開始時刻: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","0:
00"))+IF(R6>="8:00"*1,"0:15","0:00")
■休憩時間を45分として勤務時間を計算する
(提案していただいた『労働時間』です)
=IF(COUNT(C6:D6)<2,0,AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="1
3:15"*1),"12:30",Q6),"16:35")-MAX(IF(AND(C6>="12:30"*1,C6<="13:1
5"*1),"13:15",P6),"08:00")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:4
5","0:00")+FLOOR(MAX(D6,"16:35")-"16:34:59","0:15")))
■当日 勤務時間
=IF(COUNT(C6:D6)<2,0,IF(R6>="8:00"*1,R6-"0:15",R6))
うまく計算されています。
的確なアドバイス、本当にありがとうございました。
助かりました。
No.3
- 回答日時:
え~っと、未だに読解できてませんが・・・。
tascanyさんの回答で出来上がりなら、この後は無視してください。
始業時間は
~ 8:00 は 8:00
8:00~12:30 は 入力時間
12:30~13:15 は 13:15
13:15~ は なし
終業時間は
~12:30 は なし
12:30~13:15 は 12:30
13:15~16:35 は 入力時間
16:35~16:50 は 16:35
16:50~ は 入力時間
休憩時間
12:30~13:15 の 0:45 が 休憩時間
勤務時間が 8:00を超えた場合
16:35~16:50 の 0:15 が 休憩時間追加
と、いう事かな???
以前と以降が誤植だとして読解してますけど、あってます?
再度書いてくださり、ありがとうございます。
tascanyさんの回答で計算できました。説明不足で申し訳ありません。
>tascanyさんの回答で出来上がりなら、この後は無視してください。
とのことですので補足はしませんが、回答して下さる気持ちに感謝いたします。
ありがとうございました。
No.1
- 回答日時:
すみません。
条件が理解しがたいのですが・・・。就業時間: 8:30~17:05 休憩時間: 45分 h: 7:50
にしたいと言う事ですが、
休憩時間は12:30から13:15までの45分
終業時刻が16:35以降なら15分の休憩時間が加算
と、相反するのですが、どうなのでしょうか?
連続労働、何時間以上になるので15分加算って感じなのでしょうか???
この回答への補足
>終業時刻が16:35以降なら15分の休憩時間が加算
これは、今回の修正前までの条件になります。
言葉足らずで申し訳ありません。
>連続労働、何時間以上になるので15分加算って感じなのでしょうか???
労働時間8時間以上で45分に15分の休憩が加算されます。
よって、定時の労働時間7時間50分ですと、休憩45分と計算させたいと考えています。
元の式からどういじったらよいものか困り果てております…。
アドバイスあれば、よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) エクセルで休憩時間を引いての作業計画予定表の作成の仕方 2 2023/07/24 14:11
- アルバイト・パート 6時間の勤務で45分の休憩とあるが 4 2022/07/02 17:38
- 仕事術・業務効率化 IF関数で時間指定をして、数値を切り上げたいです 1 2022/05/01 23:37
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ラブホって、支払う料金は2人合...
-
複数の休憩時間がある場合の休...
-
~時間を超えるの定義
-
Excelの時間計算で経過時間から...
-
休憩中の店員
-
「お先に休憩行ってきます」と...
-
フードコートで何も頼まずに休...
-
ローソンでバイトしています。 ...
-
エクセルで休憩時間を計算した...
-
コンビニバイトで5時間の日に休...
-
お昼休憩中に寝るのがいけない...
-
休憩中に話しかけてきた客に対...
-
車初心者マークなんですが、下...
-
エクセルでの作業計算方法について
-
休憩に入っていないのにその分...
-
仕事中の休憩時間の過ごし方に...
-
休憩・残業時間の自動算出
-
昼休憩について
-
派遣社員です。休憩時間の規則...
-
工場のライン作業は、お昼休憩...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの時間計算で経過時間から...
-
複数の休憩時間がある場合の休...
-
エクセルでの作業計算方法について
-
ラブホって、支払う料金は2人合...
-
エクセルで休憩時間を計算した...
-
~時間を超えるの定義
-
フードコートで何も頼まずに休...
-
休憩中に話しかけてきた客に対...
-
仕事中の飲み物購入について
-
今度、彼女と初めてラブホに行...
-
「お先に休憩行ってきます」と...
-
車初心者マークなんですが、下...
-
エクセルで休憩時間を引く時と...
-
お昼休憩中に寝るのがいけない...
-
工場のライン作業は、お昼休憩...
-
休憩中の店員
-
コンビニバイトで5時間の日に休...
-
ここで仕事のこと愚痴愚痴言っ...
-
私は総務課です。 昼休み中に職...
-
ローソンでバイトしています。 ...
おすすめ情報