if a time is between certain hours and to return a result of Business hours or After hours

[wcngu1]

Hi guys!

Basically am trying to work out how to make a formula that automatically calculate if a timestamp lands between the hours of 7:30am and 4pm.

If it falls between 7:30am and 4pm I'm hoping for Status to say Business hours, If it falls between 4pm and 7:30am I'm hoping it says After hours but it just says afterhours for everything at the moment =/

This is the spreadsheet (press download on top right) https://drive.google.com/file/d/1UF5tjhja9CUk6yShcUHfURre1ER28cyW/view?usp=sharing

I have tried a few formulas with no success. e.g. =IF(AND(B2>E2,B2<F2),"TRUE","FALSE")


Any help appreciated, I'm pretty rusty with excel after a long stint away from it.

Cheers!

 

[Tim_Excel_]

Have a look at this link, it should address exactly what you need: https://www.extendoffice.com/documents/excel/4184-excel-check-if-time-is-between-two-times.html

 

[steve the fish]

You need to convert the date/ time to just time for the comparison so taking your formula you can change it to:

=IF(AND(MOD(B2,1)>$E$2,MOD(B2,1) < $F$2),"Business Hours","After Hours")