EXCEL: Formula to Determine whether a date is “within Business Hours” or “Outside Business

Trying to create a formula(s) for SLA report to show whether an agent acknowledged the assignment within the SLA time frame or not.

I found a formula in one of the other posts to show office hours are from Mon-Fri ONLY from 8-5:30pm I want to exclude evening, weekends, and holidays. Can it say "after hours" or "weekend" or "holiday" instead of using IN or OUT?

How to modify the formula to work? I tried other formats as well but it was the length of a novel and it didn't work.

=IF(AND(WEEKDAY(A2)>1,MOD(A2,1)*24>=8,MOD(A2,1)*24<=IF(WEEKDAY(A2)=7,14,20)
),"In","Out")
The section [7,14,20] was replaced with 17:30 (for 5:30pm)

A2=start time (DD/MM/YYYY HH:MM)
B2=end time

Welcome to the forum!

Excel doesn't have 'holidays' pre-defined, you would need to create a list of these somewhere that the formula can refer to.

Something like this should do it (not tested). The range, $C$2:$C$10 refers to a list of holiday dates.

Formula:

Please Login or Register  to view this content.

Thank you for your reply Jason.b75.
I just tried the formula and it wroks!
Brilliant!
Thank you so much Jason.b75.

Hello, I'm trying to do a similar thing but just to determine business days/hours from afterhours. I just removed the part of the formula related to the Holidays but in my case I have an error:

"You've entered too many arguments for this function"

Is there any whay to correct this?