Excel Workday function to exclude non business hours

[Sele]

Hey,

My formula below calculates the Due Date depending on a VLookup value (let's say 1 day) and excludes Weekends and Bank Holidays. I need your advise on how to exclude non business hours from the Due Date calculation.

=IFERROR(WORKDAY([@Received],VLOOKUP([@Notes],SLA,2),BankHolidays)+TEXT([@Received],"h:mm:ss"),"-")

Business hours are 08:30 - 17:00.

So if an order comes today at 16/08/2016 23:30 I need the formula to change the @Received date and time to 17/08/2016 08:30 and then my formula would work well and would add date as per lookup value, let's say 1 day, and the result would be 18/08/2016 08:30 (24 hours).
Or if and order comes today at 16/08/2016 08:01 the @Received date and time would need changing to 16/08/2016 08:30.

Thanks a lot,
Sele

[loginjmor]

Hi -

You could use the following formula:

=IF(TIMEVALUE(TEXT(J14,"hh:mm"))<K12,DATEVALUE(TEXT(J14,"mm/dd/yyyy"))+K12,IF(TIMEVALUE(TEXT(J14,"hh:mm"))>L12,DATEVALUE(TEXT(J14,"mm/dd/yyyy"))+1+K12,"-"))

This assumes your entered date is in J14, your starting business hours (8:30 AM) is in K12, and your ending business hours (17:00 PM) is in L12. If an order comes in at 8:01, on 8/16/16 (Sorry, I get confused if I work in European dates so you will have to change the formats in the formula), then it sets the date and time to 8/16/16 8:30 AM. If an order comes in after 17:00, it sets the date and time to 8/17/16 8:30 AM.

Hope this helps!

[Sele]

Thanks loginjmor, worked fine