Excel 2007 : Calculation with dates

Greetings everyone, I hope your day is going well.

Today, I was requested to look at some outage reports. These reports are in the following format.

1. 1/14/11 7:00 AM 1/27/11 11:00 AM
2. 1/29/11 12:00 PM 1/29/11 2:00 PM
3. 1/29/11 12:00 PM 1/30/11 2:00 AM

Now, what I need to do with these is grab the actual day outage amounts. What I mean by this is the following:

2 applications were down
1st application: 1/2/11 10:00 PM to 1/3/11 5:00 AM
2nd application: 1/1/11 11:00 AM to 1/3/11 3:00 PM

I only want to get the down time during 1/2/11. I want to do this per day, IE 1/1/11 had X number of hours downtime. 1/2/11 had X number of hours downtime. 1/3/11 had X number of hours downtime.

Is there a way that I can calculate this easily? I have racked my brain trying to figure this out for days. I can do an overall scope by simply doing a pivot table, but to do daily downtime has been a task.

Any help would be much appreciated on this! Thank you so much.

say you're down time is between 10:24 AM & 2:13 PM
First make sure there is a space between the minutes and the "AM/PM"
it the formula would be "=A2-A1" provided 10:24 was in cell A2 adn 2:13 was in cell A1

the amount of time in this case is 3 hours 49 mins.


Hope this helps