3桁ないし、4桁の数字を時間に換算する数式

現在、下記のような数式で3桁ないし、4桁の数字を時間に換算しています

=IF(OR(M2=0,M2="__:__"),24,(LEFT(M2,LEN(M2)-2)&":"&RIGHT(M2,2))*24)

対象のセルが0、もしくは未入力の場合表記される「__:__」という場合は24と表示するようにしています。
ここで問題が起きたのですが、0時01分～0時59分の場合、1～59の1桁ないし2桁の数字で出てしまうので
上記数式を適用するとエラーがでてしまうのです。

エラーをださずに例えば0時30分とかであれば24.5と表示されるようにするにはどのようにするのがベストでしょうか？

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

• 短時間でこれだけの方に答えて頂けるとは思っていませんでした。
  遅くなり申し訳ありません、そしてありがとうございます。
  
  もう少し具体的にお話させていただきますと
  出勤時間と退勤時間から拘束時間を割り出すために今回のようなことをしています。
  データの出力がテキストファイルベースでしか出来ず
  時間に関しては以下のような形で出てきます。
  
  9:00 ⇒900
  15:15⇒1515
  0:25 ⇒25
  0:01⇒1
  0:00⇒0
  入力なし⇒ _:_
  
  M2の部分には上記の右側のような形で出てきます。
  試行錯誤しているうちに加えて問題がでてきたのですが17時出勤で26時退勤の時は
  26ー17=9　といった形で拘束時間を算出したいと考えております。
  
  まだ皆さんの回答に目を通しきれていませんので頂いた答えを読み解きつつ
  試行錯誤してみようと思います。
  
  お礼など少々遅れるかもしれませんがご了承下さい。

    補足日時：2016/07/22 15:58

• 皆さんの回答をみつつ考えていたのですが、
  そもそも、出勤時間と退勤時間で式をわけるべきでした。
  根本的に考え直す必要があるようです。
  
  もう一度よく考えた上で改めた質問と、今回のお礼をさせていただきますので
  少々お時間を下さい。申し訳ありません。

    補足日時：2016/07/22 16:08

• 退勤時の情報として、24時を回る場合は隣のセルに『翌』という文字があることを確認できました。
  ですので、数字の隣のセルに以下の式を入力しました。
  
  =IF(O2="翌",P2+2400,P2)
  
  そして、その結果に対して最初の式を適用する形で解決するに至りました。
  
  回りくどく、とても汚い、その場しのぎの解決策となりましたが
  今回、頂いた回答を参考によりいいものを作れるよう勉強しようと思います。
  
  ご回答いただいた皆様、本当にありがとうございました。

    補足日時：2016/07/22 17:28

No.1 回答者： mike_g 回答日時： 2016/07/22 12:09

=INT(M2/100)+MOD(M2,100)/100

ではダメですか？

この回答へのお礼

そちらでは思ったような結果は得られませんでした。
ご回答ありがとうございます。

お礼日時：2016/07/22 17:00

No.2 回答者： GooUserラック 回答日時： 2016/07/22 12:28

１時間未満の時は２４時間をたすのですか？

No.3 回答者： GooUserラック 回答日時： 2016/07/22 12:45

☆１時間未満の時は２４時間を足す場合

「=IF(M2<100,INT(M2/100)+MOD(M2,100)/60+24,INT(M2/100)+MOD(M2,100)/60)」
☆そのまま表示する場合
「=INT(M2/100)+MOD(M2,100)/60」

この回答へのお礼

すぐに返答できず申し訳ありません。
私も最初は1時間未満の時間だけどうにかできればいいと考えていたのですが、根本的に間違っておりました。
ご回答ありがとうございました。

お礼日時：2016/07/22 17:02

No.4 回答者： enunokokoro 回答日時： 2016/07/22 13:58

M2のセルは、文字列として3桁または4桁の数字または「__:__」が入って

いるのですか？

とりあえず、3桁または4桁の文字列としての数字としてなら、以下の式
を使って「030」を「24.5」にすることはできます。

=(MID(TEXT(M2,"0000"),1,2) & "." & MID(TEXT(M2,"0000"),3,4))*24

このままでは小数点表示になりませんので、[セルの書式設定]を開き
[表示形式]を[数値]にして、[小数点以下の桁数]を指定しておきます。

あとはIF関数やOR関数で何とかなりませんか？

この回答へのお礼

返答が遅くなってしまい申し訳ありません。
今回は元々の式を使って解決することにしましたが
こちらでご提示いただいたものの方がよりスマートにできそうなので
今後の参考にさせていただければありがたいです。
ご回答ありがとうございました。

お礼日時：2016/07/22 17:11

No.5 回答者： moto_koukousei 回答日時： 2016/07/22 14:09

=IF(OR(M2="",M2="0",M2="__:__"),24,IF(VALUE(LEFT(M2,LEN(M2)-2))=0,24,LEFT(M2,LEN(M2)-2))+RIGHT(M2,2)/60)

上のような式を使った場合、《M2のセルが文字列指定の書式》ならば
下の画像のような結果になります。
ただ、このような式は無理矢理そう表示させているだけなので、あまり汎用性はないです。
M列には、本日?の午前1時以前 or 1時間より短時間の45分間　等は入らないのですか。　使い方や入力範囲が分からないので、想像するだけですが、上のような数式もなんか無理矢理ですし、感心しません。

この回答へのお礼

仰るとおり、どうもスマートではないといいますか、無理矢理という部分は否めないと思います。
今回は、こちらの式を流用する形で解決することにしましたが
もう少し勉強をし、汎用性のある式を作れるよう
勉強をしていきたいと思います。
ご回答ありがとうございました。

お礼日時：2016/07/22 17:13

No.6 ベストアンサー 回答者： WindFaller 回答日時： 2016/07/22 14:13

=TEXT(M2,"00!:##")*24

入力　結果
30 -> 0.5
2430 -> 24.5
--------------------
=TEXT(M2+2400*(INT(M2/100)=0),"00!:##")*24

入力　結果
30 -> 24.5
2430 ->24.5
2830 ->28.5

この回答へのお礼

いつもお世話になっております。
とてもスマートな計算方法で衝撃的です！
まだきちんと内容を把握できてないので勉強させてください。
今回、0と入力なしの場合にエラーが出ないようにしたいので
元々の式を流用する形で解決としましたが
こちらの式もきちんと理解すればエラー回避ができると思うので
少し試行錯誤をして取り込んでみようと思います。
ご回答ありがとうございました。

お礼日時：2016/07/22 17:23