エクセルで時間外を求める関数

質問

エクセルで勤務管理表を作成しています。
早出・遅刻・残業の合計時間を関数を使って求めるには
どうしたら良いでしょうか？

Ａ行には出勤時間　Ｃ行には退社時間入力

出勤時間８：００、退社時間１７：００が定時です。

８時前に出社した場合１５分単位で早出割り増しがつきます（７：２８出社なら０．５／７：４７出社の早出はゼロ）

８時１分を過ぎると８：３０出社とみなされます（０．５の遅刻）
１７時１５分を超えると１５分単位で残業がつきます（０．２５の残業）

最終的にＥ行に早出と残業の合計時間を０．２５の単位で入力したいのですが、関数を教えて下さい。

回答No.2

hirumin · ベストアンサー

[A1]：出社時間
[C1]：退社時間
だとして・・・

まず、出社時間の補正をしましょう。
・8時前なら15分単位の早出
・8時過ぎは30分単位の遅刻
ということなので、ここでは CEILING関数を使います。
指定された単位で、0から遠い方に丸める関数です。
7:28　なら　15分刻みで0から遠い方ということで　7:30 に丸めることができます。

［8時前の出社時間］=CEILING(A1,1/96)
［8時以降の出社時間］=CEILING(A1,1/48)

1/96 と 1/48 というのは、1日の1/96と1/48でそれぞれ15分、30分を示します。
この式を、8時前後という条件で分けると

［出社時間］=IF(A1>=1/3,CEILING(A1,1/48),CEILING(A1,1/96))

A1>=1/3 は、出社時間（A1）が1日の1/3（つまり8時）以上、という意味になります。

次に、退社時間の補正をします。
CEILING関数を使って15分単位にしてしまうと、17:12 は 0から遠い 17:15 になってしまいます。
ここでは0に近い方に丸める FLOOR関数というものがあるのでこれを使います。

［退社時間］=FLOOR(C1,1/96)

これで 17:12 は0に近い 17:00 に丸められます。

出社時間と退社時間が規定単位になったところで、後は引き算。

［退社時間］－［出社時間］
=FLOOR(C1,1/96)-IF(A1>=1/3,CEILING(A1,1/48),CEILING(A1,1/96))

この式を［E1］に書き込めばOK。セルの書式を「時間」にして下さい。

実際は昼休み1時間を引いたりもするのかな？
17時以前の早退は30分単位で欠勤扱いになる、というのもあったりするのかな？
後はここに上げた2つの関数を使えば対応できると思いますので、工夫してみて下さい。

回答No.1

Cupper

悪いことは言いません
会社の人に聞いたほうが早いと思います

質問の内容を見ると、質問者はどうにか Excelを使える程度のスキルであるとお見受けします
よく知っている人に直接質問しながら作成することをお奨めします

15分単位で管理したいということですが、入力は1分単位や秒単位でも入力が可能ですので
15分に満たない時間は切り捨てるのか、累計するのか、切り上げるのかなどの仕様を
ハッキリとさせましょう
仕様がしっかりと決まっていない状態で質問をされても答えようがないのです

…そんなですのでよく知っている人に直接教えてもらうことをお奨めしています

ちなみに
　× A行、C行、E行
　○ A列、C列、E列
です
行と列の表現を間違えていますので、これを機会に正しく覚えてしまいましょう