Help with time duration calculation

[TN0410]

Hello All,

Please I need some assistance with a formula to calculate the time duration between 2 dates with the following conditions:

- weekends are excluded

- work hours are between 8AM and 5PM (any request received after 5PM is treated as next day

I used this formula to get the difference between the 2 dates, =TEXT(C3-A3,"d:h:mm:ss") but I've been stuck at factoring the conditions. Im using the formula to measure adherence to SLA.

Any help will be appreciated. Cheers

[p45cal]

See the first formula here: https://exceljet.net/formula/get-wor...ates-and-times with explanations.
You'll probably need a bit more help to get the output as you'd like.
Things to consider include
how to display for example 13.5 hours? As 1.5 days (so you can easily do arithmetic on multiple results)? Perhaps as 1 day and 4:30hrs (much more difficult to do arithmetic on)
include public holidays (and/or company holidays).

The formula can get quite complicated, although more recent versions of Excel can simplify it, depending on your version of Excel; what is your version? Does it include the function LET on a sheet?
Another way would be to design a user defined function which would involve enabling macros; is that a possibility?

Best attach a workbook with some dates and times with expected results (manually calculated so that we can test the formula against them) - make the dates so that they really test the formula and try to make it go wrong!

Questions to answer in red.

[caabyyc]

it will be better for others to understand your question with sample data including original data and expected result.