Unavailability/Elapsed time Calculation

[hitha1512]

Hi Team,

I have the below raw data - Incident number, its open time and its resolved time. I need to calculate the unavailability of a device linked to incident based on few conditions as mentioned below.

Device	Incident	Open date	Closed date
Server	INC001	12/7/2022 12:45:00 PM	12/8/2022 2:50:00 PM
Server	INC002	12/8/2022 12:45:00 PM	12/13/2022 9:45:00 AM

Conditions:

> Business hours to be between 7 AM to 7 PM 

> Weekends to be excluded

Desired Output:

Device	Incident	Open date	Closed date	Output to achieve -	Details
Server	INC001	12/7/2022 12:45:00 PM	12/8/2022 2:50:00 PM	14:05 hours	12/7/2022 12:45:00 PM ---> 6:15 hours (12:45PM to 7:00 PM (Business hours)) 12/8/2022 2:50:00 PM ---> 7:50 hours (7:00AM(Business hours) to 2:50PM) Total : 14:05 hours (6:15 + 7:50)
Server	INC002	12/8/2022 12:45:00 PM	12/13/2022 9:45:00 AM	33 hours	12/8/2022 12:45:00 PM ---> 6:15 hours ( 12:45PM to 7:00 PM (Business hour)) 12/9/2022 ---> 12 hours (Fullday- 7AM to 7PM business hours) 12/10/2022 ---> 0 (weekend) 12/11/2022 ---> 0 (weekend) 12/12/2022 ---> 12 hours (Fullday- 7AM to 7PM business hours) 12/13/2022 9:45:00 AM ---> 2:45 hours ( 7AM(Business hour) to 9:45AM) Total : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45)

This will tell me that the device Server was unavailable for 47 minutes for that month.

Would appreciate any help on this 🙂

Regards,

Hitha Dhani