勤怠表について ABS、TEXT関数の使い方について教えて下さい

初めまして。2つ質問です。

①会社で勤怠表を作るに当たり、
始業時間　G5
就業時間　H5
休憩時間　I5
とし、法定外時間　K5　に
=TEXT(ABS(($H5-$G5-$I5)-TIME(8,0,0)),IF(($H5-$G5-$I5)-TIME(8,0,0)<0,"-h:mm","h:mm"))
を入力すると、残業時間（プラス表示）、早退時間（マイナス表示）が表示されるのですが、定時で残業時間ゼロの時に「-0：00」と表示されてしまいます。この-0：00を非表示にする方法が解る方がいればご教授お願い致します。
※会社は週40時間勤務内での変則時間の為、マイナス表示が必要になります。

②①のやり方だと法定外時間Ｋ列の合計はプラスの残業時間分だけなので、M列に
=TEXT(ABS("8:00"-$J5),"h:mm")
を入力し、早退時間をプラス表示しＫ列（プラス分）-Ｍ列（マイナス分）を差し引いて総残業時間を出そうと考えましたが、土日などG～I列が空欄だと「-8：00」表示、残業無しでも「0：00」が出る為非表示にしたいです。
更にＭ列の合計やＫ列との引き算も上手くいかず自力では無理かと思い質問しております。
※週40時間を超えた分が残業代になります。

※これ以外に別に良いやり方があれば併せてご教授下さい。よろしくお願い致します。

質問者からの補足コメント

• 初めまして。
  早速のご返事ありがとうございます。
  
  確かにTEXTやABSを使ったことで文字列化して面倒になっていた気がします。ご教授の
  =$H5-$G5-$I5-TIME(8,0,0)
  でＫ列の+，-時間の表示、合計が簡単に出来ました。ありがとうございます。
  
  説明不足で申し訳ございません。Ｊ列は、
  =H5-G5-I5
  の計算式です。
  弊社は変則時間制なので、週40時間に収まるように法定外時間の+,-表示が必要となります。休憩時間も各自どこかで1時間取ります。（今までは手計算でした）
  
  Ｋ列で+,-の足し算が出来るようになったので、Ｍ列は無しで、Ｊ欄が空白の場合にＫ欄も空白表示になれば全て解決かと思います。現状Ｊ欄が空白の場合はＫ欄に「-8：00」が表示され合計にも反映します。
  
  「Ｊ欄が空白の場合、K欄も空白表示」はどのようにすればよいでしょうか？
  重ねてご教授お願い致します。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2025/05/18 22:05

No.2 回答者： kagakugasuki 回答日時： 2025/05/23 04:43

=$H5-$G5-$I5-TIME(8,0,0)

だけですと、

>時刻型の数値は、内部で10進数に変換されていて少数以下の微小誤差を含む場合があります。

と同じ原因で、0:00が表示されてしまうおそれがありますので、

>エクセルの設定で「1904年から計算する」にチェックを入れておくことで可能になります。
>その上でも、0:00を表示上は空白表示にしたいのなら、セルの書式設定で
>　h:mm;-h:mm;;@

は同様に済ませておいた上で、K5セルの関数を次のようなものにされた方が宜しいかと思います。


=IF($J5=″″,″″,ROUND(($H5-$G5-$I5-″8:00″)*1440,0)/1440)

　因みに、1440を掛けているのは、Excelの時刻を表すシリアル値を｢分単位の整数値｣に変換するためで、含まれている微小誤差を無くすためにROUND関数で数値を丸めて完全な整数値にした上で、1440で割る事でシリアル値に戻しています。
　ROUND関数で数値を丸めているため、0:00付近の微小誤差も消えて、完全な0となるため、｢h:mm;-h:mm;;@｣の表示形式でも正の値の場合の｢h:mm｣形式で表示されるおそれや負の値の場合の｢-h:mm｣で表示されるおそれが無くなり、0の場合の非表示となります。

No.1 回答者： fujillin 回答日時： 2025/05/18 19:06

こんにちは

ご説明に不明な点がありますが・・・
とりあえず、入力値は全てエクセルの時刻型の数値であるものと仮定しました。

①について
＞定時で残業時間ゼロの時に「-0：00」と表示されてしまいます。
時刻型の数値は、内部で10進数に変換されていて少数以下の微小誤差を含む場合があります。
ですので、自国の入力値によっては、同じに結果になると思えても「0:00」と表示されたり、「-0：00」と表示される場合があります。
計算式の判定値に多少の誤差を含めてあげれば「-0:00」の表示は避けることができるでしょう。
具体的には、
　=TEXT(ABS(($H5-$G5-$I5)-TIME(8,0,0)),IF(($H5-$G5-$I5)<TIME(7,59,59.5),"-h:mm","h:mm"))
のように0.5秒のマイナス値までは正数で扱うようにすれば宜しいでしょう。

その上でも、『「0:00」は空白表示にしたい』というのであれば、ご提示の式では計算結果を文字列に変換しているので、「結果が0:00なら空白にする」ような式にすれば良いでしょう。
具体的には
　=IF(ABS(計算結果)>TIME(0,0,0.5),計算結果,"")
のような式にしておくことで可能になります。


一方で、文字列化した値を、その後の計算で集計したりするのは面倒なので、負の時間も扱えるようにしておく方が便利そうに感じます。
（実際に何をなさりたいのかよくわからないので、判断はできませんが）
そうすれば、ご提示の式は
　=$H5-$G5-$I5-TIME(8,0,0)
と簡単にすることも可能になります。
負の時間も扱えるようにするには、エクセルの設定で「1904年から計算する」にチェックを入れておくことで可能になります。
その上でも、0:00を表示上は空白表示にしたいのなら、セルの書式設定で
　h:mm;-h:mm;;@
などとしておくことで可能になります。
（こちらの場合は、セルは値を保持しており表示だけ空白になります）


ご提示の内容は通常の勤務計算とは異なるようなので、疑問点として、
＞定時で残業時間ゼロの時に～
のような表現がありますが、始業時刻、終業時刻が固定である場合には、ご提示の計算では正しく計算ができない可能性があります。
（休憩時間帯も固定なのかも不明ですが）
「とに角8時間出勤していて、好きな時間に1時間休憩をとる。勤務時間が8時間を超えたなら残業扱い。」という規則であるなら、ご提示の計算でも宜しいと思われますが・・
例えば「休日出勤して10:00~14:00まで就業。休憩は無し。」のような場合に、正しく集計できるでしょうか？


②について
J列の値をどのように求めているのか（あるいは手入力なのか）不明ですけれど、「J列に値がなければM列は計算しない」などとしておけば良いのではないでしょうか？
J、K列が空白になるのは休日だけではなく休暇取得とかも考えられますし、逆に、土日に出勤なども通常はあり得ると考えられますが・・・
「J列に値がなければ～」を実現したければ、
　=IF(J5="","",計算したい値)
などとしておけばよいでしょう。


＞※これ以外に別に良いやり方があれば～～
時間を文字列にしてしまっているので、その値を利用してさらに計算するのが面倒になっていると考えられます。
（↑）で示したように「負の時間も扱える」ような設定にしておき計算をさせる方が、値を全てそのまま再利用できるようになるので便利ではないかと考えますが・・・

＞早退時間をプラス表示しＫ列（プラス分）-Ｍ列（マイナス分）を差し
＞引いて総残業時間を出そうと考えましたが～～
例えば、残業（？）時間のプラス値だけを集計したいような場合に、結果を数値にしておけば SUMIF関数 などでそのまま計算できますが、ご提示のように結果を文字列にしてしまうと、判定条件がそれなりに面倒になります。