関数教えて！

質問

始めまして、エクセル２００７の関数のことで教えて欲しい事があります。　手作りで勤務表を作ったのですが、残業時間を自動的に計算して表示する関数式が分からなくって困ってます。
表はシンプルに開始時間セルと終了時間セルと残業時間セルの３項目を作っています。
開始時間と終了時間を打ち込んだら自動で残業時間を表示さしたいのですが・・・ちなみに、基本時間は８：００～１７：００休憩時間込で９時間です。それ以外を残業とみなしています。（早出もです！）

どなたか　関数に詳しい方教えてください。
よろしくお願いします。

回答No.8

_Kyle

#5です。

＞ただ、一つ「０：００」の表示の件でどうしても　
＞８：００～１７：００の時に「０：００」が出てしまいます。
＞何か良い手はありますか？

あー、確かに、8:00～17:00　のときは、
演算誤差が発生してキッカリゼロになりませんね(^^;;

「基本時間」の組み合せなのに気付かないとは
我ながら少しウカツでした。お手数おかけしてすみません。

「シリアル値　⇒　文字列　⇒　シリアル値」
で誤差を吸収するアプローチで処理します。

　=--TEXT(MAX(B2-A2-"9:00",0),"[h]:mm")

■日経PC21 / エクセル「演算誤差」対策講座
http://pc.nikkeibp.co.jp/pc21/special/gosa/index.shtml

以上ご参考まで。

回答No.7

imogasi

毎日のようにこのタイプの質問がここにでる。
質問者は過去のこのコーナーの質問や、Googleで「エクセル　勤務時間表　関数」などで照会して、勉強し、わからない点に絞って質問のこと。
課題丸投げの質問になっている。
ーー
ほかに、エクセルで、セル番地の引き算で、経過時間が出る理屈の根拠たる、エクセルの日付・時刻シリアル値に関する理解は大丈夫ですか。
Googleなどで「日付・時刻シリアル値」で照会
http://www.officepro.jp/excelfunc/date/index0.html
ほか多数出てくる。

回答No.6

米沢 栄蔵（@YON56）

この問題は、「関数に詳しい」云々の前に、正しい認識(定義)が必要です。
この問題を解決しないで、関数式を作っても意味がありません。

正しい認識(定義)とは、残業時間の定義です。
単に、残業時間=(業務終了時刻-業務開始時刻)-9時間で、いいのですか。
残時間がマイナスになる場合や、既定就業時間(休憩時間込で9時間)は満足しているが、業務開始時刻や業務終了時刻が規定外の場合はどうするのか、時刻や時間の入力形式や表示方法はどうするのか等々。
単に、残業時間=(業務終了時刻-業務開始時刻)-9時間の場合を記述します。
A1:業務開始時刻,B1:業務開始時刻,C1:残業時間としてC1の関数式は、
　=IF(OR(A1="",B1=""),"",(B1-A1)*24-9.00))
但し、A1,B1の表示形式(書式)はユーザー定義で[hh:mm]で、「8:00」と入力する必要があります。
また、C1の表示形式(書式)は[数値]で、計算値C1は時間単位の表示になります。

回答No.5

_Kyle

>残業時間のセルには時間が入力されて無いと、＃＃＃＃＃　・・？
>みたいな表示が出ています。

=MAX(B2-A2-"9:00",0)

とすれば、
・開始時刻から終了時刻までが9時間に満たない場合
・時刻が入力されていない場合
は 「0:00」 になり、オートSUMで合計を求めることができます。

なお、「0:00」ではなく空白にしたい場合は、
セルの表示形式を　[h]:mm;;;　としてください。
------------------------------------
それから、例えば、
　開始時刻　：　 9:00
　終了時刻　：　18:00
のような場合には、
#4さんが回答された数式とは異なる結果が返ります。ご注意ください。
私の数式は上記の場合、休憩込みの9時間で残業なしと判断し、0:00 を返すようにしています。

ご参考まで。

お礼

ご丁寧な回答、ありがとうございます！
おかげさまで、希望の動きになりました。
ただ、一つ「０：００」の表示の件でどうしても　８：００～１７：００の時に「０：００」が出てしまいます。何か良い手はありますか？
それ以外は、バッチリでした。
ありがとうございます！

回答No.4

KURUMITO

残業時間を求めるのですね。
次のようにしてはどうでしょう。
例えばＡ1セルに開始時間、Ｂ1セルに終了時間があるとして残業時間は次の式であらわされます。
="8:00"-MIN("8:00",A1)+MAX("17:00",B1)-"17:00"

回答No.3

takesun

9時間とそれ以外ならこれでも十分ですが、
おそらくこれだけではないと思いますけど。
　　A　　　　　　B　　　　C
1　出勤時間　退勤時間　残業時間
2　　　　　　　　　　　=B2-A2-"9:00"

何も条件がありませんので、とりあえずたたき台程度です。

回答No.2

Hachi5592

開始時刻と終了時刻のセルにHH:MMという時刻形式で入っているの
でしたら、残業時間のセルに
=B2-A2-TIMEVALUE("9:00")
と入れてみてください。
ちなみに式の例は、セルA2に開始時刻、B2に終了時刻としています。
残業時間のセルの表示形式を時刻のHH:MMとします。

補足

早速の回答、ありがとうございました。
残業時間が自動的にでました。
ただ一つ問題が・・・書くの忘れてしまった事が一つあります。
日にちごとの残業時間の合計をオートＳＡＭで出せる様になってたのですが、出なくなってしまいました。
残業時間のセルには時間が入力されて無いと、＃＃＃＃＃　・・？
みたいな表示が出ています。

合計時間は、手動で打ち込まなければやはりダメでしょうか？
追加のご回答頂ければ幸いです。

回答No.1

hallo-2007

時刻がシリアル値で入力されているのであれば、そのまま引き算が出来ますよ。
　Ａ　　　　　　Ｂ　　　　Ｃ
開始時間　終了時間

残業を9時間を越えた時間というならば
=B2-A2-"9:00"
残業を17:00以降の時間というならば
=B2-"17:00"
早出は　="8:00"-A2