ＥＸＣＥＬＬマイナスを含む時間合計（２４Ｈ超え）

質問

先日　ＳＩ２９９７９２様に
TEXT化された時間計算の合計方法を下記の如くご教示頂き
問題となっていたマイナス時間を含む合算は出来るようになりました。

=SUM(IF(W13:W43>"",SUBSTITUTE(W13:W43,"-","")*IF(LEFT(W13:W43,1)="-",-1,1)))

しかし、合計時間が２４時間を超えた段階で正しい合計時間にならなくなりました。
もともとマイナス時間を扱う為にＴＥＸＴ化しているため
セル表示を時間表示に出来ません。
現状のマイナス時間が扱えるまま２４時間越えの合計時間を計算することは出来ないでしょうか？

回答No.2

imogasi · ベストアンサー

前の質問を読んで、回答しようかと思ったら、すでに回答締め切り済みであったように思う。
（１）質問者の望むらしいニュアンス（意図）に沿った、回答を挙げる回答者が（このOKWAVEでは）多い。それが正しい態度だと思う。
小生のように、批判的に質問を見ている者は、エクセルの日付や時間に関する質問は、エクセルの日付シリアル値を勉強し、その上で回答を編み出せ、ということが多い。でも人気はないだろう。無視しているのではないかな。
ーー
本件も、テキスト化したデータで考えるとか、最後の表示形式の適用で、Text関数などで、文字化するやり方を採ったのだろう。
回答者の名前を出すよりも、質問番号を書いておいてくれた方が、考察の参考になるのだ。誰も前の質問番号なぞ覚えていないはず。
参考解説　ＴＥＸＴ関数
https://dekiru.net/article/4509/
出来るだけ日付・時刻の演算は、日付・時刻シリアル値のままで行い、最終に表示形式の設定かTEXT関数を使えば事なきを得るはず。
うまく行かないケースがあるような気はするが。
＝＝
例
A2＝＞15:23:00　時刻シリアル値
A３＝＞18:36:00　時刻シリアル値
A4に　=TEXT(A2+A3,"[hh]時mm分ss秒")で、見た目　33時59分00秒
こういう例では、計算され、表示されているのでは？
=TEXT(SUM(A2,A3),"[hh]時mm分ss秒")、=TEXT(SUM(A2:A3),"[hh]時mm分ss秒")
でも同じ。
ーー
＞合計時間が２４時間を超えた段階で,正しい合計時間にならなくなりました
文字列のままではセルの値は、計算にはつかえないのは、エクセルの常識。
他言語でも同じ。
ーー
どういうこと（式で）をやろうとしたのか、ケースを文章やデータで説明し、回答を求めるのが、質問の常道。
それもせずに、一般論的に「ぼやき」だけを出している。
初心者なら、取り敢えず、回答者に方法もまかせて、やり方を、本件を機会に広げる心構えが必要だ。初心者は回答の方法を嵌めたがる（自分が知らないのだから、嵌めざるを得ない場合が多いが）

回答No.5

heisukewada

邪道かとは思いますが、Googleスプレッドシートなら、表示形式を、”経過時間”にするだけで、普通に合計できますがExcelじゃなきゃだめですか？

回答No.4

bunjii

具体的な数表の例を示してください。
Excelの仕様では日時の負数を扱うオプションがありますので添付画像のような時間数の計算が可能です。
特別な関数を使う必要が無く「ファイル」→「オプション」→「詳細設定」→「1904年から計算する」にチェックし、「OK」ボタンで閉じる。
以上の処置で日時のシリアル値を加減乗除すれば良いでしょう。
集計結果を24時間以上のときも時:分:秒で表示するには表示形式を [h]:mm:ss にします。

回答No.3

Chiquilin

> しかし、合計時間が２４時間を超えた段階で正しい合計時間に
> ならなくなりました。
表示形式を「[h]:mm」にしていないだけじゃないの？

それにしても「答え」以外の回答をまるっきり読み飛ばしたのが
よく分かります。

=SUM(IFERROR(SUBSTITUTE(W13:W43&"/60",":"," ")/1,0))/24

回答No.1

aokii

TEXT化された時間計算の合計を２４時間を超えても表示するためには、全てのデータを、一旦、通常の数値データに直してから、計算を行う必要があります。