Count hours/minutes between dates using a custom and variable schedule

[jwillis07]

Good morning all, I'm trying to find the number of business hours/minutes between two dates.

However, not only do I have a non-standard schedule, the business hours change from customer to customer. Therefore I'm trying to find the most efficient way to do this, whilst also future proofing should one of the customers choose to change their service hours.

My example data looks like the below:

MrHolte Example Data.JPG

• I want to count the time in minutes or hours between each of the "Open" & "Resolution" dates in my "Data Table" - But only the time the business was open to that customer.
• If there is no "Resolution" date, then that row is still open and should therefore be substituted with NOW()

Using the NETWORKDAYS.INTL formula I can pass through my variable schedule dependant on the customer, however that will only count the number of business days between the two dates, and not the time.

Please Login or Register  to view this content.

If the business was open the same number of hours each day then I could just multiply the result by the hours per day, only the hours per day vary, so it isn't that simple.


Example1:

Customer - Account 1
Open Date - Monday 21/06/2021 17:08
Business Hours on Open Date: 08:00 - 18:00

Resolution Date - Monday 28/06/2021 16:45
Business Hours on Resolution Date: 08:00 - 18:00

• 52 minutes between open time and close of business
• Tuesday 08:00 - 18:00 = 10 hours
• Wednesday 08:00 - 18:00 = 10 hours
• Thursday 08:00 - 18:00 = 10 hours
• Friday 08:00 - 18:00 = 10 hours
• Saturday - Closed to customer
• Sunday - Closed to customer
• 8h 45m between start of business & the resolution time
• Total business hours between "Open" & "Resolution" = 49 h 37m

Example2:

Customer - Account 1
Open Date - Sunday 27/06/2021 07:48
Business Hours on Open Date: Closed

Resolution Date - Tuesday 29/06/2021 09:09
Business Hours on Resolution Date: 08:00 - 18:00

• No minutes on open date as the business was closed to customer
• Monday 08:00 - 18:00 = 10 hours
• 1h 9m between start of business & the resolution time
• Total business hours between "Open" & "Resolution" = 11 h 9m

I think I could work this out if it the business hours per day were all the same - it probably wouldn't be pretty, and would involve a lot of nested IFS - but I could get there. As it stands, I have no idea how to sum the number of business hours for each of the network days.

Does anyone have any efficient proposed solutions?

Thank you in advance and I'm very much looking forward to seeing what you come up with.

[Glenn Kennedy]

I see that the start time can occur OUTSIDE the working hours for the account name/day (cell N9). Is that really the case?

Edit... on reading your explanation it seems that it is really the case. That adds a level of complexity or two!!

[jwillis07]

Hi Glenn, thanks for your response. Yes, it is the case that the start time could be outside business hours.

This is because each row is an IT helpdesk ticket, and these can be raised via automatic alerts, or on an online system at any time.

[Glenn Kennedy]

Mmm. Way beyond my "pay grade", I think!!

[AliGW]

Are you still using Excel 2013? If not, please update your forum profile. Thanks.

[jwillis07]

No worries Glenn, I realise this is a quite a complex and bespoke ask.

For further clarification:

I think what I'm struggling with is instead of my NETWORKDAYS.INTL formula just counting the business days, I want it to sum the business open hours in column H instead.

With that, I could just use some nested ifs to work out the minutes the business was open for both the "Open" & "Resolution" dates.

[jwillis07]

Hi AliGW, no I'm on O365 which I believe is 2016. I'll update it now

[AliGW]

No - 2016 is a standalone version. What you now have in your profile is correct - the subscription offering.

[kvsrinivasamurthy]

In R4 then copy down

Please Login or Register  to view this content.

NOTE: There is a difference of 1 min for your calculation in thread and result in row 10. To avoid 1 min is to be added. IF required I will do it. Then In Row 4 Result will be 0:02 min. What is opinion.

[jwillis07]

Hi kvsrinivasamurthy, thank you for your reply, that's most impressive.

The calculation I included in the post regarding row 10, I didn't include the seconds so having factored those in then your solution is correct.

Whilst your solution doesn't currently include the different business opening/closing times for each "Account", I assume I could just replace references to the start time in S3 & the end time in Z3 with an INDEX/MATCH and then change the NETWORKDAYS formula to NETWORKDAYS.INTL like in my post, in order to pass through the different business days per customer?

For instance:
for "Account 1" the business is open 08:00 - 20:00 on a Monday, but "Account 2" has longer hours of 06:00 - 20:00.
for "Account 1" the business is closed on Sunday, for "Account 2", the hours are 08:00 - 18:00.

Will take me a while to break this down though and see exactly what's happening though.

[kvsrinivasamurthy]

post deleted

[jwillis07]

Yeah it's the in the file. B4:H24 contains the different timings under the heading "Business Hours Per Account", and then the bank holidays (which is universal to all customers) is J4:K11.

I have no objection to using helper columns and splitting the formula up if it makes it easier. In fact, it may help me to digest it too.

[kvsrinivasamurthy]

Pl see file. I have made some correction in AI column just now.
Helper columns are used.
In R4

=SUM($AF4:$AI4)

In AA4

=IF(O4>0,$O4,NOW())

[jwillis07]

Outstanding. Well done!

You did it exactly the way I had in mind, only much more eloquently, by calculating the minutes the business was open for both the start date and the end date, and then the networkdays in-between by summing the business hours for each (that last part I had no chance at doing).

Thank you very much, that is a huge help and one I'll definitely save for the future.

[kvsrinivasamurthy]

Thanks for feedback and rep.