エクセル ６５時間超を判定する 論理式

質問

勤務時間合計の判定が、どうしても出来ません（涙）

月間の超過勤務時間が、規定の６５時間を超過するかどうかを判定し、
６５時間超であれば、６５時間を超過した時間を算出し、６５時間内で
あればゼロ、と返す論理式が立てられません。

ＴＡＩＭＥＶＡＬＵＥ関数も使ったのですがうまくいきません。
多分、時刻（時間）表示がシリアル値０～１となっているので、
判定条件の６５Ｈが、ＰＣでは他の時刻と混同されているのだと
思います。
でもその対策がわかりません。

私の立てた論理式は
　ＩＦ（Ａ３２＞”６５：００”、Ａ３２－”６５：００”、０）
これではダメで、ＴＩＭＥＶＡＬＵＥ関数をいろいろ試しても
今のところダメです。

私の条件は
・エクセルは２００２
・全ての書式は[h]"時"mm"分"　で２４時間超でも表示可能の設定
・Ａ１からＡ３１をＡ３２で合計し、そのＡ３２を判定対象に
しています。
・今回の論理式とは無関係と思いますが、Ａ列の前に始業時刻、終業
時刻、休憩時間の入力があり、
　終業時刻ー始業時刻ー休憩時間数を自動計算し、
その結果が８時間を超過すると、超過時間をＡ列に自動入力される設定です。

どなたかお教えください。

回答No.1

mu2011 · ベストアンサー

次の数式をお試し下さい。　
ＩＦ（Ａ３２＞”６５：００”＊１、Ａ３２－”６５：００”、０）

お礼

mu2011様

仕事から戻ってご回答を拝見し、試したところ、一発で解決しました。
どうもありがとうございました。
また粗忽もので、お礼のあて先を間違えて２番目の方にしてしまい
ました。ごめんなさい。

エクセルの時間計算に不慣れなもので、本当に目からうろこの
落ちる思いです。

ところで、なぜ「＊１」しなければいけないのか、すごくフシギに
感じております。

他のご回答者の皆さまも、私の論理式の不等号の向きがうっかり
逆で、大変失礼しました。

回答No.5

mrsara

うちの会社で
{A始業時刻/B終業時刻/C休憩時間/D勤務時間}が表示されていて
E休日フラグ/F通常時間/G残業時間/H深夜時間/I深夜残業/J休日勤務は計算用に同行に配置しています。
他に参照用にK8:00　L22:00を別なセル（場所は自由）を用意します。

ABCEIは入力しますがあとは自動計算にしています。

D1＝B1－A1－C1
E1は休日手当の支給対象日に"１"を入力します
F1～J1は残業手当や深夜手当、休日手当の計算用です。
F1＝IF(D1<$K$1,D1,$K$1)
G1＝IF(D1>$K$1,D1-$K$1,0）
H1＝IF(B1>$L$1,B1-$L$1,0）
I1は深夜残業に該当する時間数を入力
J1＝IF(E1=１,D1,0）

これで一通り計算できるはずです。
ちなみに1行目用です。

お礼

mrsara様

早速のご回答をありがとうございます。
mrsara様の式はキレイに上手く作成なさってますね。
我が社の勤務形態はかなり複雑で、本当に難儀しています。実際に私が作ったものを、
これから実データを入れてみれバグ探しをしなければなりません。
これからも私の手に負えない問題が発生しましたら、過去質問回答データを検索して、
それでもダメだったらまた皆さまのお知恵を拝借しなければならないでしょうか。いやになります（溜息）

私の問題は、１番目のmu2011様の式で解決いたしました。
mrsara様はじめ皆さまに本当にお世話になりました。ありがとうございました。

回答No.4

imogasi

こんな実験はどうですか。
B1、B2、B3の書式設定をユーザー定義の[h]:mm:ss
としておく。
B3に　=IF(B3>TIMEVALUE("65:00:00"),"越え","")
か=IF(B3>"65:00:00"*1,"越え","")
を入れておく。
B1,B2に
例えば
24:45:10
41:10:00
65:55:10
のような、足して６５時間を越えるや越えないデータを色々入れて
テストしてみる。
ーー
ある解説書ではTIMEVALUE（LOTUS互換らしい）はいらないようにも読める解説があったが
=IF(B3>"65:00:00","越え","")
ではうまくいかないようだ。

お礼

imogasi様

ご回答をありがとうございました。

ご教示いただいた式と同じようなことを、素人なりにいろいろ試してみたのですが、できませんでした。

時間という連続データを、デジタル数字に変換して計算する、ＰＣの限界なのでしょうか。

imogasi様はじめ皆さまのご回答に本当に感謝いたしております。
なお私の問題は、１番目のmu2011様にご回答いただいた式で私の問題は解決しました。

回答No.3

noname#176215

意味がよく分かりませんが

=MAX(A32-"65:",0)

で問題がありますか？

お礼

Chiquilin様

お忙しいところ、早速ご回答いただきましてありがとうございます。
この論理式では、Ａ３２セルから６５時間を減算した値と０との最大値を返すことになるので、
必然的にＡ３２から６５時間減算した値となるのかな、と思ってます。
となると、私の困っていた時間計算の際のシリアル値の扱い方とは、やや異なるご教示となるのでしょうか。実際に私ので試算したところ、うまくいかないようです。
私の質問文がつたないせいで、状況が伝わらず、申し訳ありませんでした。

１番目に回答をお寄せいただいたmu2011様のご回答で、とりあえずは解決しました。

Chiquilin様はじめ、皆さま、ありがとうございました。

回答No.2

mrsara

別セルに参照用に65:00（Ａ１セルとします）を用意して
=if(A32>$A$1,A32-$A$1,0)
これで出ますよ。

お礼

mrsara様

仕事から戻ってご回答を拝見し、試したところ、一発で解決しました。
どうもありがとうございました。
エクセルの時間計算に不慣れなもので、本当に目からうろこの
落ちる思いです。

ところで、なぜ「＊１」しなければいけないのか、すごくフシギに
感じております。

他のご回答者の皆さまも、私の論理式の不等号の向きがうっかり
逆で、大変失礼しました。

補足

mrsara様
デスクに戻って慌てていたので、うっかりお礼の内容間違いをして
しまいました。大変失礼しました。

ご回答１番目のmu2011様のご回答でとりあえずは解決しました。
mrsara様のやり方もこれから試してみます。

mrsara様、mu2011様、お二人への失礼をお詫びいたします。