Minutes Calculations

[call_Vishwa]

Hi,
I am trying to automate a part of my work, which involves calculation of
downtime of servers based on the time (Core/Non Core hours). I have a sheet
with columns for Start time of the Incident, End time of the incident,
DUration of Downtime (End -startime), a computed column for Core or non core
based on the Start time...

Now I need 2 additional columns for Core Downtime and non Core Downtime in
minutes/hours. The Core Downtime would be any downtime in the Core hours. Now
the challenge here is, when a problem last for 2 days how do I compute the
Core and non core separately.

Please advise and let me know if you need more information.

Thanks in advance

 

[daddylonglegs]

Perhaps you could use something like this

Assuming core time between 08:00 and 18:00 Monday to Friday thi
formula will give you the core time where A2 is start and B2 end

=IF(OR(A2="",B2=""),"",(NETWORKDAYS(A2,B2,)-1)*("18:00"-"08:00")+IF(WEEKDAY(B2,2)>5,"18:00",MEDIAN(MOD(B2,1),"18:00","08:00"))-IF(WEEKDAY(A2,2)>5,"08:00",MEDIAN(MOD(A2,1),"18:00","08:00")))

format as [h]:mm

Clearly you can subtract this figure from the total downtime to giv
the non core hours.

Let me know if it's more complex than this, perhaps you need to includ
Saturdays etc

 

[call_Vishwa]

Thank you daddylonglegs, that was really helpfull, I do have additional
complications of Saturdays and Sundays for a few instances... How would I
incorporate that in this?

Thanks in Advance.

 

[daddylonglegs]

What are the core times