[[20191211083317]]　『特定の時間帯の場合のみ指定文字を返す。』（くーと）　ページの最後に飛ぶ

『特定の時間帯の場合のみ指定文字を返す。』（くーと）

時間帯ごとの料金表が違うため、開始時間（E5)と、終了時間（F5) がともに22:00～5:00の場合は"深夜"、そうでない場合は"通常"と返したいのですが、うまくいきません。

=IF(OR(AND(E5>=(TIME(0,,)),E5<=(TIME(4,59,)),F5>=(TIME(0,,)),F5<=(TIME(4,59,))),AND(E5>="22:00"*1,E5<="23:59"*1,F5>="22:00"*1,F5<="23:59"*1),AND(E5>=(TIME(22,1,)),E5<=(TIME(28,59,)),F5>=(TIME(22,1,)),F5<=(TIME(28,59,)))),"深夜","通常")

※TIME関数と時間と統一できてなくてすみません。

この式ですと、開始/終了時間がいずれも0：00～4：59のときと、開始/終了時間がいずれも22:00～23：59のときは正しく返ってきますが、開始時間が22時以降で、終了時間が24：00～28:59の（日付をまたぐ）ときには”通常”と返ってきてしまいます。

開始時間から終了時間の内、深夜時間帯がどれくらい含まれるかを計算する際には、
"5:00"-MIN("5:00",E5)+MIN("29:00",F5)-MIN(MAX("22:00",E5)という関数を使っているのですが、
今回は時間数ではなく"深夜"/"通常"どちらの時間帯なのかを求めたいです。

ご教示お願いします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 ＞ともに22:00～5:00の場合は"深夜"

 境目の判断が大事なので、一点再確認します。

 スタートが22:00の場合は深夜でしょうが、
 スタートが 5:00は通常でいいですね？

（半平太） 2019/12/11(水) 09:42

 時刻データに関してですが、

 出庫が翌日の4時59分の場合
 　28:59 と入力しているんですか、それとも
  　4:59 と入力しているんですか？

（半平太） 2019/12/11(水) 10:21

 ＞開始時間から終了時間の内、深夜時間帯がどれくらい含まれるかを計算する際には、 
 ＞"5:00"-MIN("5:00",E5)+MIN("29:00",F5)-MIN(MAX("22:00",E5)という関数を使っているのですが、

 その数式が正しいとした場合(ちょっと不安を感じていますが・・）

 その時間から通常時間の正味が逆算できるので、
 その正味時間がプラスなら、「通常」判定になるんじゃないですか？

（半平太） 2019/12/11(水) 11:08

早速のご連絡ありがとうございます。
22:00-28:59（4:59）までが深夜割増時間帯で、5:00-21:59が通常時間帯で大丈夫です。

終了時間のセル、F5には、24:00以降の場合、例として4:59の時には28:59と入力するようにしています。

逆算の部分、開始か終了のどちらかが深夜割増時間帯なら答えを返すではなくて、開始と終了のどちらも深夜割増時間帯の場合にのみ答えを返したいので、合計時間から深夜割増時間を引くではないのかなと思いました。
ご指摘いただいたところの理解が正しくできていなかったらすみません。
（くーた） 2019/12/11(水) 11:45

 ＞開始時間（E5)と、終了時間（F5) がともに22:00～5:00の場合は"深夜"、そうでない場合は"通常"と返したい

 私はそれを「通常」が1分でもあれば「通常」と返したい、
 と解釈しているんですが、違うんですか？

 たとえば、開始(4:00)～終了(23:00)　の場合
 　
 　開始、終了「ともに 22:00～5:00 の場合」に該当しますが、
 　中間に通常時間が入っているので、全体として「通常」ですよね？

 なので、通常が1分でも含まれれば、「通常」判定になると思われますが。

（半平太） 2019/12/11(水) 12:37

 ＞開始時間から終了時間の内、深夜時間帯がどれくらい含まれるかを計算する際には、 
 ＞"5:00"-MIN("5:00",E5)+MIN("29:00",F5)-MIN(MAX("22:00",E5)という関数を使っている

 本題じゃない部分ですけど、その式で本当に深夜時間帯が算出できますか？

 下図の時刻の場合、深夜時間帯が 0:00 となるべき所、マイナスになりましたが。

  行  _ E _  _ F _
   4  開始    終了     
   5  20:00  21:15

（半平太） 2019/12/11(水) 16:16

こんばんは。返信が遅くなりまして申し訳ございません。

＞＞開始時間から終了時間の内、深夜時間帯がどれくらい含まれるかを計算する際には、
＞＞"5:00"-MIN("5:00",E5)+MIN("29:00",F5)-MIN(MAX("22:00",E5)という関数を使っている
＞本題じゃない部分ですけど、その式で本当に深夜時間帯が算出できますか？

最後、切れてました。

="5:00"-MIN("5:00",$A$5)+MIN("29:00",$B$5)-MIN(MAX("22:00",$A$5),$B$5)

A5が開始時間、B5が終了時間なのですが、これで動いています。

＞たとえば、開始(4:00)～終了(23:00)　の場合
＞　
＞　開始、終了「ともに 22:00～5:00 の場合」に該当しますが、
＞　中間に通常時間が入っているので、全体として「通常」ですよね？
＞なので、通常が1分でも含まれれば、「通常」判定になると思われますが。

通常が1分でも含まれれば、「通常」判定になるで間違いありません。
4：00～23：00の場合は通常判定でOKです。

今の式で動かないのが、22：00～25：00、といった終了時間が24：00以降になるときです。

（くーと） 2019/12/11(水) 21:37

 ＞最後、切れてました。 
 ＞="5:00"-MIN("5:00",$A$5)+MIN("29:00",$B$5)-MIN(MAX("22:00",$A$5),$B$5)

 なるほどです。

 その式がどこかのセルに入力されているなら
 =B5-A5-(その式が入力されているセル)　> 0

 ※なお、上の数式は、小数演算誤差対策が必要になります。

 どこにもその数式が入力されて無いなら、これでいいと思います。
 　　　　　　　　　　　　　　　　　　　↓
  =IF(OR(MIN("22:00",F5)>MAX("5:00",E5), F5>"29:00"*1),"通常","深夜")

 ※こっちの式は、小数演算誤差対策は不要です。

（半平太） 2019/12/12(木) 00:06

お世話になっております。
ありがとうございます！
考え方がぜんぜん思いつきませんでしたが、上記の式で解決しました！
とても勉強になり、助かりました。
早々にご回答いただいてありがとうございました。
（くーと） 2019/12/12(木) 01:53

コメント返信：

[ 一覧(最新更新順) ]

[[20191211083317]] 『特定の時間帯の場合のみ指定文字を返す。』（くーと） ページの最後に飛ぶ

[[20191211083317]]　『特定の時間帯の場合のみ指定文字を返す。』（くーと）　ページの最後に飛ぶ