Calculating no. of business hours for enquiries received

[tsabicon1989]

Hi all,

Hoping to get some help with the following:

I'm looking to calculate the no. of business hours our enquiries have been open for, and the time taken to close enquiries (two separate lists and formulas).

For calculating enquiry open time, I want to take the current date and time and minus the date and time the enquiry was open (I'm in Australia so date formatting is dd/mm/yy. For example:

Current date and time = 24/05/2023 10:23 AM - enquiry open date and time 10/05/2023 2:44 PM. What I want to know is the total number of business hours, (represented in business days) the enquiry has been open. Our business hours are 9am to 5pm, Monday to Friday.

I'm having difficulty including enquiries that were opened outside of business hours and enquiries that have only just opened.

Similarly I am wanting to calculate the difference between enquiry open date and time and enquiry close date and time. This is what I've managed to come up with for close date and time - open date and time (open in column C and close in column D):

=MAX(IF(AND(TEXT(C43,"ddd")<>"Sat", TEXT(C43,"ddd")<>"Sun"),
IF(AND(TEXT(D43,"ddd")<>"Sat", TEXT(D43,"ddd")<>"Sun"),
NETWORKDAYS(C43,D43)-1,
NETWORKDAYS(C43,D43)
),
IF(AND(TEXT(D43,"ddd")<>"Sat", TEXT(D43,"ddd")<>"Sun"),
NETWORKDAYS(C43,D43)-1+(NETWORKDAYS(D43,D43)-1)*(MOD(D43,1)-TIME(17,0,0))/(TIME(17,0,0)-TIME(9,0,0)),
0
)
) + (MOD(D43,1)-MOD(C43,1))/(TIME(17,0,0)-TIME(9,0,0)), 0)

Previously I've done this by referencing a date calendar sheet, I am wondering if it's possible to do with a formula. Seems like it will be a very complicated one!

Thanks in advance for any suggestions!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[tsabicon1989]

Thanks for letting me know. I have attached a doc with my current attempted formulas and my expected results with some further info.

[tsabicon1989]

Thanks for letting me know. I have attached a doc with my current attempted formulas and my expected results with some further info.

Hi again,

Just wondering if anyone has any ideas for the issue I'm having.

Thanks again.

[AliGW]

This is not helpful:

*Hopefully all these expected results are correct

Expected results MUST be correct. Only add the ones that you KNOW are so.

[tsabicon1989]

Right...

Okay these expected results are correct....

[HansDouwe]

Please can you explain the first expected result (1,99).
I don't understand how you got there.

[AliGW]

I was just about to ask the same as Hans - please talk us through the manual calculation for that first result.

[kvsrinivasamurthy]

In sheet " Open Enquiry Time" in G2 copy down

Please Login or Register  to view this content.

In sheet " Enquiry time to close" in G2 copy down

Please Login or Register  to view this content.

[josephteh]

Can OP confirm result for Reference Number 2 should be:

business hours calc.png

[John Vergara]

Hi to all!

[K1] = 9:00
[J1] = 17:00

All formulas in [G2]:

Sheet "Open Enquiry Time"

=((K$1-J$1)*(NETWORKDAYS(C2,H$1)-1)+MEDIAN((WEEKDAY(H$1,2)>5)+MOD(H$1,1),J$1,K$1)-MEDIAN((WEEKDAY(C2,2)<6)*MOD(C2,1),J$1,K$1))/(K$1-J$1)  

Sheet "Enquiry time to close"

=((K$1-J$1)*(NETWORKDAYS(C2,D2)-1)+MAX(MOD(D2,1),J$1)-MEDIAN((WEEKDAY(C2,2)<6)*MOD(C2,1),J$1,K$1))/(K$1-J$1)  

Check file. Blessings!

[tsabicon1989]

Hi all,

Apologies I haven't responded earlier, thank you for all your help! John and kvsrinivasamurthy formulas for the first calculation (Open Enquiry Time) are working. Thank you so much!

For Time to Close, apologies, I have had to go back to the drawing board.

For enquiries that are closed outside of business hours, I would like the close time to be updated to the end of the previous business day. So, for example if an enquiry was closed on Sunday - 4/06/2023 1:13 AM - I would like the close time to be Friday 2/06/2023 5:00 PM. negative numbers I will simply round up to 0.

I have attached a spreadsheet with requirements and expected results (and how I get to these results).

Thanks again,

[HansDouwe]

One way, please try

Please Login or Register  to view this content.

[tsabicon1989]

Many thanks Hans,

that looks to be correct!

[HansDouwe]

You are Welcome. Glad to have helped.

Please considder adding reputation to any helpers you think they deserve.
In that case please click * Add Reputation left below their answers.