Excelで勤務表を作成

質問

過去の質問を探してみたのですが、検索の仕方が悪いのか自分の求めるような回答を見つけることができませんでした。

現在、Excel2010を用いて勤務表を作成しているのですが、
関数を使って残業時間を算出するところで分からなくなってしまいました。

E2＝退出時間　　J2＝定時時間　　
上記の2項目は最初から入力されています。

任意のセルに以下のように
=MAX(0,E2-$J$2+IF($J$2>E2,1))
と入力しました。

翌日にまたぐ残業時間も上記の式で計算できるようになったのですが、
早退、つまり定時時間前に退出した人がいた場合
それも翌日をまたいだ残業とみなされて計算されてしまいます。

ちなみにソースとなる勤務表には退出時間は24時間表示で、
例えば午前2時に退出した人は「26:00」ではなく「2：00」と入力されています

早退をした人の残業時間はブランクか、もしくは「0:00」と表示させたいと思っています。
一つ関数で出すことが難しければ、いくつかの手順を踏んでも構いません。

以上、お知恵を貸していただければと思います。
よろしくお願いします。

回答No.4

aokii

=MAX(0,ROUND(E2*1440,0)-ROUND($J$2*1440,0)+IF(ROUND($J$2*1440,0)>ROUND(E2*1440,0),1))

回答No.3

bunjii

>早退をした人の残業時間はブランクか、もしくは「0:00」と表示させたいと思っています。
定時の数式「=MAX(0,E2-$J$2+IF($J$2>E2,1))」は判断要素が不足していますので「早退、つまり定時時間前に退出した人がいた場合それも翌日をまたいだ残業とみなされて計算されてしまいます。」となるように指定しています。
1勤務が24時間以上の場合は日付の要素も組み込まないと計算できませんが24時間未満であれば退出時刻が出勤時刻より小さいときに退勤時刻+1と言う判定にすれば解決できると思います。

1勤務が24時間未満（D列に出勤時刻がセットされているものとすれば）
=MAX(0,E2-$J$2+IF(D2<E2,0,1))
1勤務が24時間以上（F列に1を入力したときは24時以降の退出とする）
=MAX(0,E2-$J$2+F2)

回答No.2

RandenSai

まず、出勤時刻＜退勤時刻の関係を固定化し、例えば8時間を超えたら残業みたいにしないと、数字だけを見て早退と残業が区別できないのでは？そのためには、日付をまたぐ時間表示を26:00などという形式にする必要があるでしょう。

24時間を超える場合の表示形式設定
http://trendy.nikkeibp.co.jp/article/qa/trouble/20031031/106404/?rt=nocnt

そういう表示形式が許容できない場合は、どこか別のセルに早退であることを示す値（区別できれば値自体はなんでもいい）を入れておき、それを式で見て判定するくらいしか、手はないと思いますが。

回答No.1

yhabnwesoigyh

その式の外側にIF文で条件をつければよいだけでは？
もし退出時間が定時（出勤時間と退社時間）の間であれば0：00とする。
そうでなければ今作られている式の値とする。