24時間以上の表示

質問

いつもお世話になります。
Excelで勤務実働時間の変換作業を行ってます。

元のデータは"文字列"で表示されてて、"=TIMEVALUE(A1)"を使って
12:1　→　12:01　と表示させてます。
そこで質問ですが、"24:1"以上の場合ですが、"TIMEVALUE"を使うと当然"00:01"に表示されるので、
=IF(TEXT(LEFT(A1,2),"#,###")>="24",TIMEVALUE(A1)+"24:00"
と数式を入れました。続いて、同じ様に"IF"を並べて">=96"、">=72"、">=48"と作ったのですが、今度は"100:00"以上の場合です。同じく
=IF(TEXT(LEFT(A1,3),"#,###")>="120",TIMEVALUE(A1)+"120:00",IF(TEXT(LEFT(A1,3),"#,###")>="100",TIMEVALUE(A1)+"96:00"を追加してみたのですが、そうすると今度は
"100:1"以上はうまく表示できたのですが"36：1"以上"99：59"いかが正しく表示されなくなりました。中身を確認すると"=TEXT"表示に問題があるようです。
長々と説明させていただきましたが、ようするに、3桁～6桁の文字列表示の時刻を"TIMEVALUE"に表示させ、それを元に関数を使ってまた別の計算式を作りたいのです。
解りにくい内容ですみませんがよろしくお願いします。

回答No.5

KURUMITO · ベストアンサー

解答Ｎｏ2です。確かに文字列としてＡ1セルに24：10と入力すれば=TIMEVALUE(A1)での表示は0：10と表示されセルの表示形式を[h]:mmとしても24：10と表示されませんね。そこでいろいろと苦労されているのでしょう。次のようにすることで解決すればよいでしょう。
=DATEVALUE(A1)+TIMEVALUE(A1)
もちろんセルの書式設定では[h]:mmのようにします。
これでＡ1セルに文字列として24:10と入力されれば24:10と、37:00と入力されれば37:00と表示されます。もちろんこのようにして変換した時刻は計算に使うことができ、ＳＵＭ関数でも正常に使うことができます。

お礼

大変、大変遅くなって申し訳ありません。m(--)m
もう、誰も答えてくれないかと半ば諦めてました。他の作業もありそちらに気をとられながら、また時間作業に戻って"教えてgoo"を確認してみたら!!皆さんちゃんともう一度回答をくださってて感謝感謝です。
すごく簡単で解りやすい数式で感動しました。
ありがとうございました!!

回答No.4

poohron

#1です。

再度ご確認いただけないでしょうか？
私の回答で使用している関数は
TIMEVALUE ではなく、VALUE です。

TIMEVALUE 関数を使用した場合は、おっしゃる通り
24:00以上の値を扱うことができずに
24:1 は 00:01 として扱われてしまいますが、
VALUE 関数を使用することにより
24:1 は 24:01 として扱うことができ、
書式設定 [h]:mm により表示も 24:01 となります。
もちろん、SUM関数による計算等も可能です。
（動作確認済み）

お礼

大変、大変遅くなって申し訳ありません。m(--)m
半ば諦めていたもので…
ホントです。大きな勘違いをしてました。すみません。
"TIME"が付かないだけでこんなに一瞬でできるのですね。。。私の苦労はなんだったのか…
ありがとうございました!!すっきりです!!

回答No.3

yone_sk

これでどうでしょうか？

=TIMEVALUE(A1)+CONCATENATE(24*(ROUNDDOWN(VALUE(MID(A1,1,FIND(":",A1)-1))/24,0)),":00")

補足

大変、大変遅くなって申し訳ありません。m(--)m
もう、誰も答えてくれないかと半ば諦めてました。
確かにこちらの数式でもなります!!見たことない関数を利用されてるので今後の参考にもなりました。
ありがとうございました!!

回答No.2

KURUMITO

例えばＣ列に時間を表示している場合に24時間以上になる場合の表示を例えば25:30のように表示させるにはＣ列を選択してから右クリックして「セルの書式設定」を選択し、「表示形式」のタブで「ユーザー定義」とし、種類の窓に　[h]:mm　のように入力すればよいでしょう。
複雑な計算をしているようですがその必要は全くありません。

補足

それでは、ダメなのです。。。。

回答No.1

poohron

使用する関数と書式設定をすることで
とても簡単にご希望の表示をすべて実現できます。

まず、式はたったこれだけ。
=VALUE(A1)
そうすると、場合分けでの面倒な計算もまったく不要です。

しかしこれでは、やはり24:00以降は正しく表示されないので
書式設定で正しく表示されるように変更します。
セルの書式設定の表示形式からユーザー定義を選択し
[h]:mm
と設定してみてください。

もちろん、これを元に関数を使ってまた別の計算式を作ることも可能です。

補足

それではだめなのです。
セルの書式設定の表示形式からユーザー定義を選択し
[h]:mm
に設定してるんです。試してみてください。
セルA1を書式設定の表示形式から"文字列"に設定し、"25:1"と入力し
セルB1に書式設定の表示形式から"[h]:mm
"に設定し"=VALUE(A1)"を入力してください。"1：01"になりませんか。
回答No.2,KURUMITO様の答えは"=SUM()"等の関数式が使えません。"=A1+A2"なら計算できますが。。
良いお知恵を。。。