エクセルの時間計算の関数について教えて下さい

質問

セルへ時間を入力し、別のセルで４時間３０分前の時間を計算する関数を教えて下さい。
＊２時３０分から８時００分と１８時００分から１７時３０分は、４時間３０分の時間に含まずに計算します。

例えば
時間入力　　 表　示
１５：００　⇒　１０：３０
０９：００　⇒　２３：００　　（ここが上手く出来ません）
１９：３０　⇒　１４：３０　　（ここが上手く出来ません）
です。

よろしくお願いします。

回答No.5

noname#70958 · ベストアンサー

条件を整理すると、
　[入力時刻]　　 　　[表示時刻]
　00:00～02:30　⇒　入力時刻－04:30
　02:30～08:00　⇒　22:00　≡　00:00－02:00
　08:00～12:30　⇒　入力時刻－04:30－05:30
　12:30～17:30　⇒　入力時刻－04:30
　17:30～18:00　⇒　13:00　≡　00:00－11:00
　18:00～22:30　⇒　入力時刻－04:30－00:30
　22:30～00:00　⇒　入力時刻－04:30
ということですから、

適当なシートの適当なセル範囲（仮に同じシートの$F$2:$H$8とします）に
　00:00　1　04:30
　02:30　0　02:00
　08:00　1　10:00
　12:30　1　04:30
　17:30　0　11:00
　18:00　1　05:00
　22:30　1　04:30
と入力しておけば、

　=MOD(A1*VLOOKUP(A1,$F$2:$H$8,2,1)-VLOOKUP(A1,$F$2:$H$8,3,1),1)

で結果が出ます。

また、お奨めはしませんが、別表を作らず直接数式に埋め込むこともできます。

　=MOD(SUMPRODUCT(CHOOSE({1,2},A1,-1)*VLOOKUP(TEXT(A1,"hh:mm"),{"00:00",1,"04:30";"02:30",0,"02:00";"08:00",1,"10:00";"12:30",1,"04:30";"17:30",0,"11:00";"18:00",1,"05:00";"22:30",1,"04:30"},{2,3},1)),1)
-----------------------------------------------------------------
いずれの数式も、(小数誤差の出方を除けば）#3さんが提示されたものと同じ挙動ですが、
#4さんが提示されたものとは、
・02:31～07:59
・17:31～17:59
・18:00～22:29
の範囲について挙動が違いますのでご注意ください。
（Excel2003で動作確認）

以上ご参考まで。

お礼

ご回答ありがとうございます。

入力ミスしてもフォローしてくれることに感動しました。

どうもありがとうございました。

回答No.4

Wendy02

こんばんは。

ご質問の内容を全部入れると、こんな感じかな。
少し、数式に無理がありますが、なんとか読めるかと思います。

=IF(OR(AND(A1>"17:30"*1,A1<"18:00"*1),AND(A1>"2:30"*1,A1<"8:00"*1)),"入力ミス",IF(AND(A1-"4:30"<("8:00")*1,A1-"4:30">("2:30")*1),A1+1-"4:30"-"5:30",IF(AND(A1>("17:30")*1,A1<("18:00")*1),A1-"5:00",IF(A1-"4:30"<0,A1+1-"4:30",A1-"4:30"))))

お礼

ご回答ありがとうございます。

"時間"と表現することを初めて知りました。
この部分をこれから勉強します。

ありがとうございました。

回答No.3

DIooggooID

今仮に、Ａ１セルに時間を入力するとすれば、その計算式は、以下のようになります。

=IF(A1>=(22.5/24),A1-(4.5/24),IF(A1>=(18/24),A1-(5/24),IF(A1>(17.5/24),(13/24),IF(A1<=(2.5/24),A1+(19.5/24),IF(AND(A1>(2.5/24),A1<(8/24)),(22/24),IF(A1<(10/24),A1+(14/24),IF(A1<(12.5/24),A1-(10/24),A1-(4.5/24))))))))

お礼

ご回答どうもありがとうございました。

時間は数値化するときは時間／２４にしないといけないことを忘れていました。

ありがとうございました。

回答No.2

shut0325

No.1です。

＞17:30:00から17:59:59の範囲内であれば　11:00:00

13:00:00の間違いです。

お礼

ありがとうございます。

私も間違っていました。
すみませんでした。

回答No.1

shut0325

＞（ここが上手く出来ません）
こうあるからには現状のコードがあるわけですよね？
それを公開されると具体的な回答が得られやすいかと思います。

ちなみに入力した時間が
2:30:00から7:59:59の範囲内であれば　22:00:00
17:30:00から17:59:59の範囲内であれば　11:00:00
を返すのですよね？

お礼

ご回答ありがとうございます。

２時３０分から８時００分と１７時３０分から１８時００分の間の時間は入力しません。
その場合は入力ミスとしますが、もし入力ミスもエクセルの関数で１１時００分に表示出来れば嬉しいです。

コードはなく、セルへ時間を入力して関数で自動計算させた値を求めたいです。

よろしくお願い致します。