System Uptime Excluding Weekends & Time Windows

[dgarden]

New to the board and kind of a noob with Excel in general.

I am trying to track server uptime. I've created a spreadsheet that we are going to use to track times when the server was down. So in A1 I've got "Outage Start," B1 "Outage End" and C1 "Total Interruption" in minutes. I'm recording events in this format: "2/16/12 4:56 PM"

The tricky thing is that I only care about calculating service interruptions between 6:00 AM - 6:00 PM Monday - Friday. Anything outside that window should not be counted. Make sense?

I've literally been trying to come up with this all day long and am stuck. Any help would be greatly appreciated!

 

[guerillaunit]

Put this in column C

=IF(AND(WEEKDAY(A3) > 1, WEEKDAY(A3)<7, TEXT(A3, "hh:mm") > "06:00", TEXT(A3, "hh:mm") < "18:00"), B3-A3, "Not in Range")

This will exclude any downtime that started on the weekend or started between 6:00 PM and 6:00 AM

 

[dgarden]

Thanks, but that doesn't seem to work. If I enter 2/16/12 5:09 PM in A2, 2/17/12 7:19 AM in B2, it displays "Not in Range."

I want it to calculate the time between 5:09 PM - 6:00 PM on the 16th, and 6:00 AM - 7:19 AM on the 17th.

Is what I'm trying to do even possible as a formula?

 

[dgarden]

Oops, sorry - it kind of works I guess. The example above doesn't work though, nor does one that starts during the week but ends in a weekend. I don't want to count those hours...

 

[barry houdini]

Try the formula I suggested here

That calculates Monday to Friday hours between 08:00 and 17:00, you can adapt for any time period...

 

[dgarden]

Thanks Barry! That's exactly what I needed. I can't tell you how I much I appreciate it!!!