みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

残業時間だけを数式で計算したい(エクセル2007出来れば、2000でも

残業時間だけを数式で計算したい(エクセル2007出来れば、2000でも見れたほうがいい)のですが、
計算式の答が ###########になってしまいます。

始業時間が9:00 (セルA1)
退勤時間が17:20 (セルB1)
他休息時間    (セルC1)
お昼休憩が0:50
基本勤務時間7:30

超過勤務時間だけを計算したいので、
IF(A1=0,"0:00",(B1-A1)-"7:30"-"0:50"-C1)
と入力しています。

計算結果が上手く表示される方法を教えて下さい。
セルの表示設定は ユーザーでh:mm を選択しています。

よろしくお願いします。

投稿日時 - 2010-06-10 13:04:27

QNo.5958400

困ってます

質問者が選んだベストアンサー

>計算式の答が ###########になってしまいます。

この原因はセルの値がマイナス値になったときに表示されますが、提示された数式では、小数点以下の数値の演算を行っているので、エクセルの丸め誤差がでるため、実際は残業なし(ちょうど7時間30分)にも関わらず(ごく僅かにマイナス値となるため)この表示となることがあります。

たとえばC1がゼロで始業時間が9:00、退勤時間が17:20 なら0よりわずかに大きい値になり(正常な表示)、始業時間が10:00、退勤時間が18:20 なら0よりわずかに小さい値(エラー表示)となります。

確認するにはセルの書式を数値にして桁数を15桁表示させてみてください。

このエラーを避けたい場合は数式に微小値を加えた以下のような計算式に変更します。

IF(A1=0,"0:00",(B1-A1)-"7:30"-"0:50"-C1+10^-10)

ちなみに、計算式が実際に負の値になるときに0と表示したい場合は、以下のような数式にするのが簡単です。

IF(A1=0,"0:00",MAX(B1-A1-"7:30"-"0:50"-C1,0))

投稿日時 - 2010-06-10 15:45:24

お礼

ありがとうございます!

まさしく求めていた答えです。
セルの値が数値にすると割り切れない数字だったので、これが原因かな、と思ってはいたのですが、
確証と解決する方法が分からなかったので、質問させていただきました。
(ただ、+10^-10が分からないままですが、頑張って調べます!)

本当にありがとうございました。

投稿日時 - 2010-06-10 16:32:06

このQ&Aは役に立ちましたか?

4人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

こんにちは!
すでにNo.1さんが仰っているように
マイナスの場合は質問のような状態になります。
回避方法はオプションから「1904年から計算する」にチェックをいれると
マイナス表示も可能ですが、残業時間なのでその必要もないと思います。

質問そのままの数式を利用させていただいて
=IF(OR(A1=0,(B1-A1)-"7:30"-"0:50"-C1<0),0,(B1-A1)-"7:30"-"0:50"-C1)

としてみてはどうでしょうか?

尚、余計なお世話かもしれませんが、セルの表示形式が h:mm だということですけど
[h]:mm にすれば 24時間を超えても表示できます。
(1日の残業時間なので実際は24時間を超えることはないと思いますが…)

以上、参考になれば幸いです。m(__)m

投稿日時 - 2010-06-10 13:43:55

お礼

ありがとうございました!

数式をそのまま利用させてもらったら、ちゃんと表示されました。
丁寧に解説して頂き、とても助かりました。

投稿日時 - 2010-06-10 16:39:45

ANo.1

ただ、単に退勤時間から残業していなくて計算結果がマイナスなので「####」とでているだけではないですか?

それなら、IF文で退勤時間-始業時間が7:30を超えている場合のみ計算させるようにすればどうでしょう?

投稿日時 - 2010-06-10 13:14:55

お礼

早速のお返事ありがとうございます。

頑張って、やってみます。

投稿日時 - 2010-06-10 16:41:00

あなたにオススメの質問