Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 : Excel 2010)
2種類ある残業時間を求める計算式について教えてください
投稿日時: 19/01/22 10:47:47
投稿者: kidoagito

勤務終了時間と残業時間内での中抜けを手入力して、割増なしの残業時間と割増の残業時間を求めたいです。
 
下記表のE6、E7,F6,F7に入る計算式が知りたいです。
 
手入力部分は以下です。
 C6、C7に終了時間、D6,D7に中抜け時間-中抜け時間は残業時間から除きたい
また、曜日によって、終業時間が異なります。
 月曜なら、割増無しの時間は16:50〜17:40、(それ以降は割増あり)
 火曜なら    〃    17:00〜17:50    〃 
 
    A    B     C      D         E        F
1        始業    終業     割増開始        
2    月    8:40    16:50      17:40        
3    火〜金    8:50    17:00      17:50        
4                        
5    日    曜    終了時間  中抜け    割増なし    割増あり
6    11    月    20:43            
7    12    火    21:15       0:24    
 
よろしくお願いいたします。    

回答
投稿日時: 19/01/22 11:43:59
投稿者: んなっと

「中抜け」の時間は「割増なし」から引くのか、「割増あり」から引くのか不明です。
 
「割増あり」から引く場合。
 
    A   B     C     D     E     F
1     始業   終業 割増開始          
2   月 8:40   16:50   17:40          
3 火〜金 8:50   17:00   17:50          
4                           
5   日  曜 終了時間  中抜け 割増なし 割増あり
6   11  月   20:43   0:24   0:50   2:39
7   11  月   17:10        0:20   0:00
8   12  火   21:15   0:24   0:50   3:01
9   12  火   17:10        0:10   0:00
 
E6
=MAX(0,IF(B6="月",MIN(D$2,C6)-C$2,MIN(D$3,C6)-C$3))
下方向・↓
F6
=MAX(0,C6-IF(B6="月",D$2,D$3)-D6)
下方向・↓

投稿日時: 19/01/22 11:54:03
投稿者: kidoagito

んなっと様
 
申し訳ありません。
 
ご質問の件ですが、最初に「割増無し」から引いて、それ以上に中抜け時間がある場合は、「割増あり」から引く、という計算式でお願いします。
 
以上

回答
投稿日時: 19/01/22 12:45:57
投稿者: んなっと

無償のQ&Aなのですから、希望している結果をE:F列に書くのが礼儀です。
質問文を作成するのに、最低2時間はかけてください。
 
    A   B     C     D     E     F
1     始業   終業 割増開始          
2   月 8:40   16:50   17:40          
3 火〜金 8:50   17:00   17:50          
4                           
5   日  曜 終了時間  中抜け 割増なし 割増あり
6   11  月   21:00   0:20   0:30   3:20
7   11  月   21:00   1:00   0:00   3:10
8   12  火   21:00   0:20   0:30   3:10
9   12  火   21:00   1:00   0:00   3:00
 
E6
=MAX(0,MIN(IF(B6="月",D$2,D$3),C6)-IF(B6="月",C$2,C$3)-D6)
下方向・↓
F6
=MAX(0,C6-MAX(IF(B6="月",D$2,D$3),IF(B6="月",C$2,C$3)+D6))
下方向・↓

投稿日時: 19/01/23 09:40:54
投稿者: kidoagito

ありがとうございました。