Formula to Assign whether a date is "In Business Hours" or "Outside Business Hours" in excel

Question

I'm trying to create a formula to determine whether a phone call was made inside or outside of business hours based on the day and time of the call

Business hours are Monday to Friday 8AM to 8PM and Saturday 8AM to 2PM.

So essentially what I need to create the formula for is;

if call is Monday, Tuesday, Wednesday, Thursday, Friday and time is >8 and <20 or call is Saturday and time is >8 and <14 then show "Inside Business Hours"

For everything else show "Outside Business Hours"

Thanks

Answer 1

This formula should be sufficient:

=IF(AND(WEEKDAY(A2)>1,MOD(A2,1)*24>=8,MOD(A2,1)*24<=IF(WEEKDAY(A2)=7,14,20)),"In","Out")

Answer 2

These are the opening hours in the range D1:G8:

The date and time is in A2: 04.09.2017 20:00:00

This is the formula: =IF(AND(TIME(HOUR(A2);MINUTE(A2);SECOND(A2)) > VLOOKUP(WEEKDAY(A2);E$2:G$8;2;FALSE)/24;TIME(HOUR(A2);MINUTE(A2);SECOND(A2)) < VLOOKUP(WEEKDAY(A2);E$2:G$8;3;FALSE)/24); "inside"; "outside")

I hope I got the English names correct. This is my German original:

=WENN(UND(ZEIT(STUNDE(A2);MINUTE(A2);SEKUNDE(A2)) > SVERWEIS(WOCHENTAG(A2);E$2:G$8;2;FALSCH)/24;ZEIT(STUNDE(A2);MINUTE(A2);SEKUNDE(A2)) < SVERWEIS(WOCHENTAG(A2);E$2:G$8;3;FALSCH)/24); "inside"; "outside")

Answer 3

If you dates are starting in cell A2 , please enter this formula in cell B2 and drag it to the bottom. your dates should be in this format 9/16/2017 1:05:00 PM.

=IF(WEEKDAY(A2,2)=7,"Outside of Business hours",IF(WEEKDAY(A2,2)=6,IF(AND(MOD(A2,1)<TIME(14,0,0),MOD(A2,1)>TIME(8,0,0)),"Inside Business Hours","Outside of Business Hours"),IF(AND(MOD(A2,1)<TIME(20,0,0),MOD(A2,1)>TIME(8,0,0)),"Inside Business Hours","Outside of Business Hours")))

Answer 4

You may try below formula.

=IF(AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7),IF(AND(A1-INT(A1)>=TIMEVALUE("08:00 AM"),A1-INT(A1)<=TIMEVALUE("08:00 PM")),"Inside Business Hours","Outside Business Hours"),IF(WEEKDAY(A1)=7,IF(AND(A1-INT(A1)>=TIMEVALUE("08:00 AM"),A1-INT(A1)<=TIMEVALUE("02:00 PM")),"Inside Business Hours","Outside Business Hours"),"Outside Business Hours"))

See image for reference.

In the above formula,

• WEEKDAY(A2) will give day of the week (by default, it gives 1 for Sunday and 7 for Saturday)
• A2-INT(A2) will extract Time from the DataTime cell
• TIMEVALUE("08:00 AM") will give decimal number of the time in the string