Using the IF statement formula below, can I make any bank holiday dates regardless of time to theatre show as "out of hours" ? using a named range

Question

=IF(AND(MOD(J2,1)>TIME(8,15,0),MOD(J2,1)<TIME(17,0,0)),"In hours","Out of Hours")

Formatted for readability:

=IF(
    AND(
        MOD( J2, 1 )  >  TIME(  8, 15, 0 ),
        MOD( J2, 1 )  <  TIME( 17,  0, 0 )
    ),
    "In hours",
    "Out of Hours"
)

UK bank holidays financial year 23-24:

07/04/2023
10/04/2023
01/05/2023
08/05/2023
29/05/2023
28/08/2023
25/12/2023
26/12/2023
01/01/2024
29/03/2024

Answer 1

Tweaking JNevill's formula I did the following: I first check the list of bank holidays for the match, if that causes and error because it's not a bank holiday it then validates as it previously did; however, if it doesn't produce an error, that means it's a bank holiday therefore it is labelled "Out of Hours". Modify ranges as necessary.

=IF(ISERROR( VLOOKUP(INT(J2) ,$Q$1:$Q$10,1,0)) ,IF(AND(MOD(J2,1)>TIME(8,15,0),MOD(J2,1)<TIME(17,0,0)),"In hours","Out of Hours"),"Out of Hours")