TEXT関数（負の値）を集計のため数値に変換したい

小さな会社の勤務管理表を自作中です。

残業時間を集計したいのですが、遅刻をする人がいるためマイナスになる場合があり、TEXT関数で以下のように1日の残業時間の合計を出しました。

＝TEXT(ABS(J20-K20),IF(J20>=K20,”h:mm”,”-h:mm”))

※J20は残業時間合計です（朝の時間外、昼休み残業、時間外残業の合計）
※K20は遅刻時間です

月の残業時間を集計するため、上の式で出た残業時間を数値化したいのですが、”-h:mm”で出たマイナスの値が#VALUE!エラーで数値化できません。
どうしたら数値化して集計可能になるでしょうか？

上記式でも、”h:mm”で出た値は、セルに*1で数値化でき合計できました。

No.4 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/05/16 21:22

こんばんは！

No3様のご指摘のように、テキスト化してしまうと集計できません。
月の残業時間を集計するだけなら、下記の数式で可能です。
J列-K列（残業時間合計－遅刻時間）の結果をSUMPRODUCTして、TEXT関数で表示する方法です。

残業時間合計（J1:J31）遅刻時間（K1:K31）

残業集計するセルへ
IF(
　SUMPRODUCT(J1:J31-K1:K31)<0,
　"-"&TEXT(-SUMPRODUCT(J1:J31-K1:K31),"h:mm"),
　TEXT(SUMPRODUCT(J1:J31-K1:K31),"h:mm")
　)

この回答へのお礼

ありがとうございます。
教えていただいた内容で無事に集計できました。

当初、*1でテキスト関数で表示された残業時間合計を数値化してそちらを集計しようとすると、1日あたりの残業時間合計がマイナスとなった場合、#VALUEのエラーが出て集計できませんでした。

教えて頂いた式ですと、*1の数値化は行わず、そのままテキスト関数を使用した残業時間合計の欄を残したまま、月の合計残業時間が集計可能となりましたので、ベストアンサーに選ばせて頂きました。

尚、労働時間から差し引くものというご指摘も最もかと思いましたが、今現在、手動（紙&電卓）計算で給与計算をされている70代の方が遅刻の場合残業から差し引くようにされており、常勤のため基本給は固定となっておりますので、このように計算させていただきました。

また実際には、残業代がマイナスとなることはなく、シュミレーション上マイナスとなる場合も考慮したものですが、残業代がマイナスとなったことはないです。

見習い中のため、給与計算、エクセルとも初心者で申し訳ありません。皆さまのご丁寧な回答に心より感謝申し上げます。

お礼日時：2022/05/22 12:12

No.5 回答者： oniwabangairu 回答日時： 2022/05/17 09:36

＞　出た値は、セルに*1で数値化でき合計できました。

下のようなのでも、何かの参考になるのかな

No.3 回答者： fujillin 回答日時： 2022/05/16 09:07

こんにちは

エクセルのオプション設定で「詳細表示」で、「1904年から計算する」にチェックを入れます。
その上で、ご提示の式は、
　=J20-K20
に変更します。
以上で、お望みの結果になると思います。

※ ご提示の式のように、テキスト化してしまうと上手くいきません。

No2様のご指摘も、もっともだと思いますけれど・・

No.2 回答者： chonami 回答日時： 2022/05/16 05:39

そもそも遅刻時間は労働時間から引くものですから、時間外の集計から引くのはおかしくないですか？

これで0以下になったら割増分がなくなるということですか？

No.1 回答者： mygoonickname 回答日時： 2022/05/15 23:15

こんばんは。

時刻のマイナスは不可ですので、普通の数字ではダメでしょうか？
1:00を数字の１とか、1:30を数字の1.5とか。

=(J20-K20)*24

セルで、数字の１は、１日＝24時間の意味になります。
0.5で12時間、0.1で2.4時間ですかね。
1時間＝1/24になるので、24を掛けると時間になります。