Work out the HH:MM difference between 2 dates during business hours

[bentunnicliffe]

Hi, im looking to create a spreadsheet that measures the total Service Downtime during a Major Incident (MI) for my organisation. For those that dont know, a MI is when a certain service or IT application isn't working (very simply put!).

The thing im struggling with is we only measure service downtime during our service operating hours, which are Monday - Friday, 08:00am - 17:30pm.

Examples:
1. If an MI started at 17:00 on a Tuesday and was resolved at 08:30 on the Wednesday, that would be a service downtime of 1 hour 0 mins (17:00-17:30 tue & 08:00-08:30 wed = 1 hour).

2. If an MI began at 15:00 on a Friday and was resolved at 09:00 on the Monday, that would be a service downtime of 03h30m (15:00-17:30 fri & 08:00-09:00 mon = 3 hours 30 mins).

Im not skilled enough to do this myself, I can work out time difference but not excluding times outside our service operating hours.

Thank you!

 

[vletm]

bentunnicliffe​

One sample ...
Usage:
Modify "Inits"-sheet as needs
> Holiday > whole days will skip
> Weekday > daily working hours

"Calculation"-sheet
> write start and end -datas ... There have to be both date and time.
You can add as many rows data as needs.

>> Press [ Do It ]-button to get results.
Ps This is 3rd version of this kind of calculation.

 

[bentunnicliffe]

bentunnicliffe​

One sample ...
Usage:
Modify "Inits"-sheet as needs
> Holiday > whole days will skip
> Weekday > daily working hours

"Calculation"-sheet
> write start and end -datas ... There have to be both date and time.
You can add as many rows data as needs.

>> Press [ Do It ]-button to get results.
Ps This is 3rd version of this kind of calculation.

You genus, thank you so much! You have no idea how much this is going to help our service!

I just have 1 slight issue, I ave inputted all our holiday dates into the Holiday table but the macro isnt picking them up, how would I change this? I have attached it again so you can see.

 

[vletm]

bentunnicliffe​

Sat is like Saturday.
Sun is like Sunday.
If there (Inits-sheet) are no start & end-time in some weekdays then those days will show if needed.
>> Of course, You have to test ... test ... and do more tests to verify that those calculations shows correct verified results. <<

 

[vletm]

bentunnicliffe​

Your holiday-list ... yes!
You seems to copy & paste those somewhere ...
Any of those Your pasted values are NOT dates ... all of those are texts which looks like dates.
One possible is to delete those four first characters in front of those values ... then those will be dates.

 

[bentunnicliffe]

bentunnicliffe​

Sat is like Saturday.
Sun is like Sunday.
If there (Inits-sheet) are no start & end-time in some weekdays then those days will show if needed.
>> Of course, You have to test ... test ... and do more tests to verify that those calculations shows correct verified results. <<

bentunnicliffe​

Your holiday-list ... yes!
You seems to copy & paste those somewhere ...
Any of those Your pasted values are NOT dates ... all of those are texts which looks like dates.
One possible is to delete those four first characters in front of those values ... then those will be dates.

Amazing, thank you again!! Hopefully last question and no worries if this cant be done. The data will be going in via Power Automate and the dates and times will be separate, instead of having 2 columns of 'Start' and 'End', can there be 4 with 'Start Date, 'Start Time', 'End Date & 'End Time'?

No worries if not, I can possibly change the Power Automate to match this sheet.

 

[vletm]

If You would have given exact details in the beginning then those would be solved at once.
You wrote only and only about times ... and with those parameters there could be some minor challenges.
... I don't have ... any Power Automate, nor its giving output.

 

[bentunnicliffe]

If You would have given exact details in the beginning then those would be solved at once.
You wrote only and only about times ... and with those parameters there could be some minor challenges.
... I don't have ... any Power Automate, nor its giving output.

Thats fine, thank you again for all your help.

 

[vletm]

Of course,
if Your could send for me valid Excel file,
which You can get from Your Power Automate
I could check - how to use it?