勤務表を作成しています。
終了時刻ー開始時刻ー休憩時間という具合に当日の勤務時間を出力させるようにしています。
当日の勤務時間を出力するセルには以下のような条件で計算式を書いています。
<例>
=IF(COUNT(C13:D13)<2,0,IF(AND(C13<="9:00"*1,D13>="17:40"*1,"18:1
4"*1>=D13),"7:40"*1,IF(AND(C13>="12:00"*1,"13:00"*1>=C13,D13>="1
7:40"*1),"4:40"*1,IF(AND(D13>="12:00"*1,"13:00"*1>=D13),"3:00"*1,
FLOOR(D13-P13-E13,"0:15")))))
(1)開始、終了時刻が入力されていない時、0:00と表示。
(2)開始時刻9:00(もしくはその時刻より前)、
終了時刻17:40から18:14の間の時刻で入力された時、7:40と表示する。
(3)開始時刻12:00から13:00の間の時刻、
終了時刻17:40で入力された時、4:40と表示する。
(4)終了時刻12:00から13:00の間の時刻で入力された時、3:00と表示す
る。
(5)(1)~(4)の条件以外は終了時刻ー開始時刻ー休憩時間という計算を
行い、15分単位で丸める。
(1)~(4)の条件は変更なしで、(5)の条件を変えます。
(5)終了時刻が18:15以降、当日勤務時間が7時間40分以上になる。
7時間40分から15分単位で丸める。
<例>
開始時刻9:00、終了時刻17:40の時、勤務時間7時間40分。(定時)
開始時刻9:00、終了時刻18:14の時、勤務時間7時間40分。
開始時刻9:00、終了時刻18:15の時、勤務時間7時間55分。
(一番上に書いたの計算式だと勤務時間が7時間45分になる)
開始時刻9:00、終了時刻18:16の時、勤務時間7時間55分。
※P13は9:00以前の開始時間を9:00と考えさせる、
12-13時の時間を13時と考えさせる、
上記以外はそのまま表示させるという計算式を書いたセル。
(5)のような条件を加えた計算式が可能であれば教えて下さい。(アドバイス、ヒントでもかまいません。)
よろしくお願いします。
使用ソフト:Excel2002
No.2ベストアンサー
- 回答日時:
5番の条件変更で、休憩時間はお昼の1時間に固定され超過勤務以外は
15分で丸めないことになったとします。
=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")
-"1:00"
+FLOOR(MAX(D13, "18:00")-"17:59:59", "0:15"))
E列に「お昼の休憩以外に休んだ時間」を書かせて引いてやるとか、
遅刻や早退の分も15分単位で丸めるなどの処理は適当に追加してやっ
て下さい。
grumpy_the_dwarfさん、ありがとうございます。
当日勤務時間のセルに教えていただいた計算式を記述したところ、このように出力させたい!と思っていた通りに計算されました。
時間を割いて、回答していただきありがとうございました。
教えていただいた計算式をしっかり見て、勉強させていただきます。
No.1
- 回答日時:
考えてみたのですが一度に求める数式を書く自信がなかったので、作業列を設けて段階的に作ってみました。
C列:開始時刻、D列:終了時刻、E列:休憩時間ですよね。
15分単位で丸めるのは18:15以降の勤務に対してで切り上げですよね。(それとも勤務時間が7時間40分を超えたときでしょうか。9:00開始以外のケースが不明)
計算上の開始時刻(G列)
:=IF(COUNT(C13:D13)=2,IF(C13<=TIME(12,0,0),MAX(C13,TIME(9,0,0)),MAX(C13,TIME(13,0,0))),TIME(0,0,0))
計算上の終了時刻(H列)
:=IF(COUNT(C13:D13)=2,IF(D13<TIME(13,0,0),MIN(D13,TIME(12,0,0)),IF(D13<=TIME(18,14,0),MIN(D13,TIME(17,40,0)),D13)),TIME(0,0,0))
定時内勤務時間(I列)
:=MIN(H13,TIME(17,40,0))-G13-E13
残業時間(J列)
:=IF(H13>=TIME(18,15,0),CEILING(D13-TIME(18,15,0),"0:15"),TIME(0,0,0))
※18:15以降の勤務時間に対して15分単位に切り上げて丸めています。
1日の勤務時間
:=I13+J13
ta123さん、ありがとうございます。
別のセルに記述し、実行してみたところ、
開始時刻9:00、終了時刻18:15の時、勤務時間は7:20と表示されました。
これだと、定時で終了した時(定時は開始時刻9:00、終了時刻17:40、勤務時間7:40になります)より勤務時間が少ないことになってしまいます。
時間を割いて、回答していただきありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
- 仕事術・業務効率化 IF関数で時間指定をして、数値を切り上げたいです 1 2022/05/01 23:37
- docomo(ドコモ) 通話録音が(最初から)自動的に始まるアプリは? 7 2023/01/16 15:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
フリー wifi でwindowsの時刻が...
-
[MS Access]クエリで変換 hhmm...
-
午後11時59分って何時? 昼の11...
-
SL(DL)やまぐち号 津和野行きの...
-
下の画像の問題の(2)なのですが...
-
8時間以上だったら1時間マイ...
-
iPhoneのボイスメモ
-
サンライズエクスプレスの通過時刻
-
米原駅から栃木県足利市八幡町...
-
エクセルのNow関数の時刻の更新...
-
VBA で PCの 時刻を サーバー時...
-
電線等の入線潤滑材の代用品っ...
-
アルファエックス
-
時刻と時間を明確に区別したい...
-
発車後の指定席を買えないのは...
-
無人駅では切符が買えません。 ...
-
JR特急わかしお号について
-
新幹線の自由席に座れなかった...
-
伝説の特急「まつかぜ」号、最...
-
路線バスにまだ車掌いる所ある...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Accessの重複なしのカウントの...
-
[MS Access]クエリで変換 hhmm...
-
アルファエックス
-
米原駅から栃木県足利市八幡町...
-
下の画像の問題の(2)なのですが...
-
8時間以上だったら1時間マイ...
-
iPhoneのボイスメモ
-
電線等の入線潤滑材の代用品っ...
-
午後11時59分って何時? 昼の11...
-
Excelでミリsecまでの2つの時...
-
エクセルで早朝深夜労働時間を...
-
時間と時刻の違い!?
-
標準時間と117の時間の10秒のずれ
-
[h]:mm形式→10進法への変換
-
エクセルVBAでタイマーコントロ...
-
エクセルのNow関数の時刻の更新...
-
サンライズエクスプレスの通過時刻
-
時間帯別作業時間についてエク...
-
SL(DL)やまぐち号 津和野行きの...
-
PC間の時刻同期コマンドが失敗...
おすすめ情報