[[20200908181443]]　『時間外のデータを“Off “としたい』（とらきち）　ページの最後に飛ぶ

『時間外のデータを“Off “としたい』（とらきち）

こんにちは。

B列にデータ取得日として、2020/09/08 18:16 のように表記されるセルがあります。

C列に、祝祭日と平日の9:00～17:30以外のデータは”Off それ以外は“On”となるように数式を書きたいのですが、どのようにしたら良いでしょうか？

祝祭日のリストは、「List」と名前を付けたシートのV2～V62に入力しております。

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

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

とりあえず、日にち部分と時刻部分を分けて考えてみてはどうでしょうか

■１
まず、休日かどうかを判定するには、その【日】がリストにあるか調べればokですよね。

 =IF(COUNTIF(List!V2:V62,INT(B1)),"休日である","休日ではない")

要は、COUNTIFの結果が１ならリストにあるし、0ならリストにないということです。

■２
次に、時間外かどうかを判定するには、時刻部分に着目して、このような感じでよいとおもいます。

 =IF(AND((B1-INT(B1))>="9:00"*1,(B1-INT(B1))<="17:30"*1),"時間内","時間外")

これをちょっと変形して↓のようにすれば、時間内なら0、時間外なら1になるように出来ます。

 =IF(AND((B1-INT(B1))>="9:00"*1,(B1-INT(B1))<="17:30"*1),0,1)

■３
ここまでくれば、■１と■２の結果を足し算すれば、休日or時間外なら１以上、平日の時間内なら0となる数式がつくれますよね？

 =COUNTIF(List!V2:V62,INT(B1))+IF(AND((B1-INT(B1))>="9:00"*1,(B1-INT(B1))<="17:30"*1),0,1)

■４
あとは、IF関数を使って1以上なら[off]、そうでなければ[on]と表示すればよいでしょう

 =IF(COUNTIF(List!V2:V62,INT(B1))+IF(AND((B1-INT(B1))>="9:00"*1,(B1-INT(B1))<="17:30"*1),0,1),"off","on")

（もこな２） 2020/09/08(火) 19:48

ちなみに、"平日"というのが土日以外ということなら↓でわかりますね。

 =IF(WEEKDAY(B1,2)>=6,"土日です","平日です")

（もこな２） 2020/09/08(火) 19:55

もこな2様

早速にありがとうございます！
今は検証出来ないので、明日やってみます。

ありがとうございました。
（とらきち） 2020/09/08(火) 21:09

 ＞祝祭日と平日の9:00～17:30以外のデータは”Off それ以外は“On”

 よくわかりませんけど ↓ で合ってますか？

 　　　　　　　　　　9:00～17:30　　　　左記以外
 祝日　　　　　　　　On　　　　　　　　 Off
 平日　　　　　　　　On　　　　　　　　 Off
 土日（祝日除く）　　On　　　　　　　　 On

 以上、確認だけ
（笑） 2020/09/09(水) 09:19

説明がヘタですみません。

土日、祝日　→ off
平日　9:00～17:30以外→ off
平日　9:00～17:30 on

としたいです。
（とらきち） 2020/09/09(水) 10:30

 日時データが B1セルだとして

 C1 =IF(AND(WORKDAY(B1-1,1,List!$V$2:$V$62)=INT(B1),MOD(B1,1)>="9:00"*1,MOD(B1,1)<="17:30"*1),"On","Off")

 以上
（笑） 2020/09/09(水) 11:53

笑　様？

無事、解決しました。
ありがとうございました！
（とらきち） 2020/09/09(水) 18:11

コメント返信：