Excel 勤務時間の計算式 でいい案ないですか？

会社の勤務票を作成中です。
現在、以下の計算式を用いて管理していましたが
以下の計算式では年休時、欠勤時、休日出勤時の管理が正確に入力できていないことが判明しました。

本来、
欠勤時は　　稼働時間=0.0時間 所定時間=7.5時間（例）
年休時は　　稼働時間=7.5時間 所定時間=7.5時間（例）
休日出勤時は稼働時間=7.5時間 所定時間=0.0時間（例）

となるべきなんですが、以下の計算式だと

欠勤時は　　稼働時間=0.0時間 所定時間=0.0時間
年休時は　　稼働時間=0.0時間 所定時間=0.0時間（例）
休日出勤時は稼働時間=7.5時間 所定時間=7.5時間（例）

となってしまっていたようです。

【計算式】
A1　業務開始時間（手入力）
B1　業務終了時間（手入力）
C1　休憩時間（手入力）
D1　稼動時間⇒=IF(OR(A1="",B1=""),0,B1-A1-C1)
E1　所定時間⇒=IF(D1>$M$44,$M$44,D1)　*M44=8:00（当社の所定時間）
F1　時間外残業⇒=D1-E1

詳しい方で、ご教授願えないでしょうか
宜しくお願い致します。

No.4 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2008/05/20 17:20

そりゃ、勤務体制や勤務表への記入様式によりますよ。

たとえばステータス列が{"";"宿直";"宿明";"休出";"年休"}のいず
れか1つを取ることにしましょうか。""は日勤もしくは欠勤です。日
勤の後で宿直すると、その日の勤務記録は2行になることにします。
すると所定時間が、
=choose(match(ステータス,{"";"宿直";"宿明";"休出";"年休"},min(稼働時間,"8:00"),"6:00","9:00","0:00","0:00")
になったりします。

そうじゃなくて、あくまでも1日が1行で「宿直」とか「宿明」とい
うのはその日の日勤の所定時間への加算を表すんだとすると、
=min(稼働時間,"8:00")*(ステータス<>"休出")+choose(match(ステータス,{"";"宿直";"宿明";"休出";"年休"},"0:00","6:00","9:00","0:00","0:00")
が所定時間になります。

稼働時間は、同様に加算するか時間外の方で処理するか会社の規定
次第って感じですが、方法的には上記の所定時間のやり方が流用で
きますよね。

この回答への補足

grumpy_the_dwarさんの作成例を元に作って見たんですが
なぜかエラーになってしまいます。

=MIN(H13,"8:00")*(D13<>"休出日勤")*(D13<>"休出宿直")*(D13<>"休出宿明")+choose(match(D13,{"";"宿直";"宿明";"休出日勤";"休出宿直";"休出宿明";"年休"},"0:00","6:00","9:00","0:00","0:00","0:00","0:00")

ステータス＝D13
稼動時間＝H13

ステータスは宿直　宿明　休出日勤　休出宿直　休出宿明　年休
としています。

どこがおかしいかおわかりになりますでしょうか？

補足日時：2008/05/21 13:55

No.3 回答者： grumpy_the_dwarf 回答日時： 2008/05/20 09:22

その日が年休になってるとか休日だとかの情報を入力せずに、年休

の場合の所定時間がどうこう言っても意味がないでしょう。休日出
勤もそうです。1列作って、「年休」とか「休出」なんてステータス
を入力すべきです。そうすれば見やすいし、稼働時間や所定時間の
計算で考慮できるでしょう。

たとえば所定時間。今の式は短く書くと=min(稼働時間,"8:00")
ということで、稼働時間が0なら0になりますし、稼働時間が0でなけ
れば0にはなりません。ステータスが「休出」のときに強制的に0に
したいなら、=min(稼働時間,"8:00")*(ステータス<>"休出")なんて
ヤリます。稼働時間も同様に現状=(B1-A1-C1)*and(A1,B1)であると
ころに、(ステータス="年休")*"7:30"あたりを足してやります。

この回答への補足

grumpy_the_dwarf様

ご回答頂きまして、有難う御座います。

所定時間は教えていただいた計算式でうまくいきました。

稼動時間についてもうすこし詳しくご教授いただけないでしょうか？
私が勤務している、会社の勤務体型は日勤と宿直があり
所定時間も日勤=7:30　宿直=6:00　宿明=9:00　となっております。
この場合、稼働時間に入れる式はどのようにつくったらよいのでしょうか？

宜しくお願い致します。

補足日時：2008/05/20 10:01

No.2 回答者： esupuresso 回答日時： 2008/05/20 09:01

「勤務表の作成」でサーチしてみました

http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCal …

作り方は色々ありますが、給料計算となると後で間違っていたでは済まなくなります

作ってみた結果を電卓などで再確認するようにして絶対間違いがないか徹底する事が第一の課題です

No.1 回答者： dummyplug 回答日時： 2008/05/20 05:00

うーん、「～となるべきなんですが」と書いてある内容（計算規則）がまだ全然反映されていない計算式になっていますね…。

それはそれとして直すとして、使う側は
勤務時：開始時刻(A1)、終了時刻(B1)、休憩時間(C1)を入力
欠勤時：開始時刻または終了時刻を入力しない（未入力のままにしておく）
という想定だと思う（計算式を見る限り）のですが、
年休はどうやって判断しているのですか？それとも今はそのExcelシートには年休関係の情報が入っていませんか？

入っていないのならこうやって年休情報をいれておいたらどうか、という提案になりますし、どこかのセルやシートを参照したら年休日かどうかわかるのならそれを参照するように計算式を書きます。

この回答への補足

dummyplugさま

早速の回答ありがとうございます。
年休については別セルでCOUNTIFを使って管理しております。

補足日時：2008/05/20 08:11