24時をまたぐ勤務時間のエクセル関数式の求め方

条件１．コアタイムは21時から1時だが、早出残業も発生
条件２．開始時刻はA1セルに22:00と入力、終了時刻はB1セルに2：00と入力し、
　　　　C1セルは勤務時間を時刻ではなく、
　　　　数値（2：30に終了したときは4:30ではなく、4.5）を表示させたい。

　　　　様々な数式を試し、偶然にも？下記の時に、正しい数値を求めることができました。

　　　　A1セル　22:00　（表示形式時刻）
　　　　B1セル　1900/1/1 2：30　（表示形式時刻でセル上は2:30と表示）
　 　　 C1セル　＝IF(OR(A1=""),0,(B1-A1))*24　（表示形式数値で小数点第一位まで表示）

B1セルになぜか1900/1/1も入力すると正しい数値が求められます。他の年月日を色々と試しましたが、C1の数値は正しくなりませんでした。

誤入力を防ぐためにも、B1セルには時刻（2:30）だけを入力したいと考え、
1900/1/1を入力しない方法（C1セルの関数式の見直し）はありますでしょうか？
また、なぜ1900/1/1もあると正しい数値が求められるのか、お分かりになりますでしょうか？

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

• 

  上記のＷｅｂ式では、C1は数値の4.5にならないと思いますが

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/11/29 12:59

No.4 ベストアンサー 回答者： tom04 回答日時： 2018/11/29 20:16

こんばんは！

「シリアル値」なるものを検索してみてください。
シリアル値とは　1900/1/1　を「1」として1日増える毎に「1」ずつプラスします。
ちなみに今日（11/29）のシリアル値は「43433」という整数になります。

そして小数点以下の数値が「時刻」扱いになります。
どこか使っていないセルに「12：00」と入力　→　セルの表示形式を標準にしてみてください。
「0.5」という表示になるはずです。

質問文にある
＞B1セルになぜか1900/1/1も入力すると・・・
とは　1900/1/1 2:00　のように入力した場合のセルの表示形式を標準にしてみてください。
1.08333・・・
となっていますよね。

結局　A1セルに　22：00
B1セルに　2：00　と入力しただけでは
「0.08333・・・」という数値から「0.9166・・・」という数値を引くことになり、
結果そのものがマイナスになってしまいます。
この場合、Excel的には「表示不可能」というコトで「####」だけが表示されてしまいます。

一番簡単な方法としては
夜中の「2：00」　の場合　→　「26：00」のように入力すれば解決します。

どうしてもそのまま　「2：00」のように入力したい場合は少し手間をかけてやる必要があります。

C1セルの数式を
=B1-A1+(B1-A1<0)

としてみてください。
これは　B1-A1の結果がマイナスの場合はその計算結果に「1」をプラスする！という数式になります。
これで時間表示はできるはずです。

＞4:30ではなく、4.5
というコトなのでそれも考慮すると
=ROUND((B1-A1+(B1-A1<0))*24,1)

といった感じでしょうかね。

※　小数点以下第二位を四捨五入し、小数点以下1桁表示にしていますので、
結果が中途半端な時間
例えば計算結果が　4：45　とか　4：50　でも　4.8　と表示されます。

この回答へのお礼

返事が遅れて申し訳ありません。
回答された式で、問題が解決しました。
ありがとうございます。
シリアル値は面倒くさそうなのでスルーしていましたが、関数式を使用する以上は、理解しておかなければいけないものなのですね。

お礼日時：2018/12/06 08:28

No.3 回答者： admiral_ya 回答日時： 2018/11/29 19:12

1日以内なら、

=IF(A1="",0,IF(B1<A1,1+B1-A1,B1-A1))*24
で書式フォーマットを数値にすれば良いと思います
数値の大小を見ているだけなので、甘い判定だと思いますが

No.2 回答者： raraririruru 回答日時： 2018/11/29 13:13

>上記のＷｅｂ式では、C1は数値の4.5にならないと思いますが

https://www.wanichan.com/pc/excel/2003/05/167.html

No.1 回答者： raraririruru 回答日時： 2018/11/29 11:59

https://enterprisezine.jp/iti/detail/690