他の質問を確認しましたが、当てはまるものが見つかりませんでした。すみませんが教えてください。
勤務時間の計算で、定時間内分、時間外分の割り振り方を教えてください。
定時間内勤務は8:30~17:30(休憩通常1H、通常8時間勤務)です。
8:30~17:30は定時間、それ以外は時間外とします。
割り振りは1日ごとに行い、範囲は0:00~24:00とします。
入力は下のようにします。
A1:開始時間 B1:終了時間 C1:定時間内休憩 D1:時間外休憩 E1:定時間内 F1:時間外 G1:休日
A1、B1は[hh]:mmで入力(15分単位)。C1~F1は0.00(H)を0.25(15分単位)単位で入力または表示します。
たとえば、A1:7:30、B1:22:30、C1:1.0、D1:0.5のとき、E1:8(H)、F1:5.5(H)のように、E1とF1を自動で計算させたいのです。
G1は休日フラグとし、0なら平日で時間内がある、1なら全て時間外とします。
勤務は不規則で以下のようなケースもあります。
●定時間にかからないケース(1)↓
A1=1:00、B1=8:00、C1=0、D1=0.75、G1=0のときE1=0、F1=6.25
●定時間にかからないケース(2)↓
A1=17:30、B1=23:30、C1=0、D1=0、G1=0のときE1=0、F1=6
●定時間にかかるケース(1)↓
A1=5:00、B1=15:15、C1=0.75、D1=0、G1=0のときE1=6、F1=3.5
●定時間にかかるケース(2)↓
A1=11:00、B1=24:00、C1=1.0、D1=0.5、G1=0のときE1=5.5、F1=6
●定時間にかかるケース(3)↓(極端な例)
A1=0:00、B1=24:00、C1=1、D1=2、G1=0のときE1=8、F1=13
●休日のとき
A1=6:30、B1=18:30、C1=0、D1=1、G1=1のときE1=0、F1=11
・・・のようにE1、F1を求めたいのです。
複雑な式になるかと思いますが、よろしくお願いいたします。
No.1ベストアンサー
- 回答日時:
E1=IF(G1=1,0,MAX(MAX(MIN(B1*24,17.5),8.5)-MIN(MAX(A1*24,8.5),17.5)-C1,0))
F1=IF(G1=1,(B1-A1)*24-D1,MAX(MIN(B1*24,8.5)-MIN(A1*24,8.5)+MAX(B1*24,17.5)-MAX(A1*24,17.5)-D1,0))
早速の回答ありがとうございます。
書き忘れてしまったのですが、excel2007を使用しています。
mshr1962様、察していただきましてありがとうございます。
試してみます。ありがとうございました。
No.3
- 回答日時:
以下は、アドバイスです。
勤務時間のルールは結構 IF文を多用します。
後から式を見ても良くわからなくなることもあると思います。
列の後ろの方に
A B C D E F G H I J K
早出残業時間 午前 午後 定時後残業 休日計算
とか作業列作って
E列やF列に IF文で =IF(G2=0,G2+J2,K2) *残業の場合
とかにしておくとメンテナンスもやりやすいかと思います。
作業列が目障りであれば、非表示にしておきます。
アドバイス恐れ入ります。
見えないところにワークエリアを作ってやる手法ですね。
今回は、この手法を使用しない前提がありました。
質問の時に書いておく必要がありましたね。
大変申し訳ありませんでした。
アドバイスいただきありがとうございました。
No.2
- 回答日時:
E1 :
=IF(G1<>1,IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24,0)
F1 :
=IF(G1<>1,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24+IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- その他(Microsoft Office) IF関数について教えてください 2 2022/05/10 13:31
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- Excel(エクセル) 隣り合っていないセルを まとめて税込表示したい 8 2022/09/25 14:32
- Excel(エクセル) エクセル関数教えてください 3 2022/06/21 10:22
- Excel(エクセル) エクセルの表で A1にデータの入力規則でリストを作って、 (リス、キリン、ゾウとします) リストを選 4 2022/07/15 09:29
- Excel(エクセル) ある範囲のセルに何を入力すると、別のセルに○を表示させる 3 2022/04/05 15:51
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) エクセルの関数について教えてください。 A1=B1*C1*D1の時 C1D1のセルのどちらかがが空白 1 2022/05/07 23:01
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
このプラスチックのケースの使い道
-
CDケースの開け方が分かりません…
-
CDケースの寸法
-
(昔の?)レンタル用DVDケースは...
-
急ぎ UV除菌ケース(ボックス)に...
-
免許証、学生証の紛失 一昨日、...
-
乗り換え駅トイレにスマホ落とした
-
手帳型ケースがついたままで使...
-
HDDがおかしくなりました...
-
商店街の暴走事故
-
ケース(12枚入り)割れになり...
-
ケチャップ・とんかつソース・...
-
パソコンを捨てるために、物理...
-
お菓子の内容量が減ってる!! ...
-
ケースファイルの取手について。
-
CDケースの傷
-
貴金属インゴット50gのレプリカ...
-
シリアル番号
-
Excel関数について
-
PCケースだけを新しいものに交...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
CDケースの寸法
-
最近気がついたら回答削除ばっ...
-
交換したグッズに陰毛が?
-
CDケースの開け方が分かりません…
-
丸いブラスチック製のケースの...
-
商店街の暴走事故
-
革製品の床面
-
「様」と「さん」 敬称のつけ...
-
バスマニアの方にお聞きします。
-
CDについてる帯は保管しますか
-
テプラをはがしあと
-
ノートパソコンは、キャリーケ...
-
ケチャップ・とんかつソース・...
-
PS3にFAT32でフォーマットしたH...
-
令和元年に買ったスマートフォン
-
別れて1週間の元彼に友達として...
-
レコード会社『ジェイストーム...
-
姉と弟が肉体的に交わることな...
-
このタイプの差し込み口ってな...
-
スマホを踏んでしまった事はあ...
おすすめ情報