How to sum daily durations from date ranges

[MechEngSk1]

Hi.

I'm setting up a worksheet to analyse downtime records for equipment which is populated through a link from access database.
In the data file are the entries for each downtime event. When it started, finished and the duration in minutes.
Data:
[TABLE="width: 535"]
<tbody>[TR]
[TD]Area
[/TD]
[TD="align: right"]Start Time
[/TD]
[TD="align: right"]Finish Time
[/TD]
[TD="align: right"]Duration of Event
[/TD]
[/TR]
[TR]
[TD]Crushing
[/TD]
[TD="align: right"]1/07/2013 12:34
[/TD]
[TD="align: right"]1/07/2013 12:54
[/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TD]Crushing
[/TD]
[TD="align: right"]3/07/2013 15:14
[/TD]
[TD="align: right"]3/07/2013 15:45
[/TD]
[TD="align: right"]31
[/TD]
[/TR]
[TR]
[TD]Crushing
[/TD]
[TD="align: right"]3/07/2013 15:56
[/TD]
[TD="align: right"]6/07/2013 17:10
[/TD]
[TD="align: right"]4394
[/TD]
[/TR]
</tbody>[/TABLE]


In the analysis worksheet is a list of dates that span the data range. For each of these dates, I need to sum up the total minutes for each downtime event that happened on that date. For example:
Result:

[TABLE="width: 90"]
<tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]Day
[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]Total downtime for day
[/TD]
[/TR]
[TR]
[TD="width: 75, bgcolor: transparent, align: right"]1/07/2013
[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]20
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/07/2013
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/07/2013
[/TD]
[TD="bgcolor: transparent, align: right"]515
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/07/2013
[/TD]
[TD="bgcolor: transparent, align: right"]1440
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/07/2013
[/TD]
[TD="bgcolor: transparent, align: right"]1440
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/07/2013
[/TD]
[TD="bgcolor: transparent, align: right"]1030
[/TD]
[/TR]
</tbody>[/TABLE]


I wanted to use SUMIF(), but that wont not work as far as I'm aware.
Some of the events running across several days would need to have their durations split up for each day.

The below if statements describe the three types of downtime event cases.These work great if one cell is analysed at a time, but when I need a whole range of data to be added into one cell on the following code, it does not.

IF(Date=Start Time,((Date+1)-Start Time)*24,"")
IF(AND(Start Time<date,date<finish Time),24,??)
IF(Date=Finish Time,(Finish Time-Date)*24,"")
IF(Date=Finish Time,(Finish Time-Date)*24,"")
<date,date<finish Time),24,??)
<date,day<finish Time),24,??)
<date,date<finish Time),24,??)

It soon became evident I won't be able to achieve what I need to do using functions. I have tried to get the code out for this, but to no luck. Would someone be able to help me with this code? It has been a struggle to sort out.

Thanks,</date,date<finish></date,day<finish></date,date<finish></date,date<finish>

 

[MikeWx]

Thanks for your feedback.
You can select Formulas, Formula Auditing, Evaluate Formula to step through the formula as it processes. It evaluates each of the Start Date Time and Finish Date Time timeframes with the current Column F Date, using the first of the following rules that applies to each pair:
1. If the Start Date is after the Column F Date, the value is zero because this timeframe is after the Column F Date.
2. If the Finish Date is blank, the Column F duration is the Column F Date plus one minus the greater of the Column F Date or the Start Date Time.
3. If the numeric Finish Date is before the Column F Date, the value is zero because the timeframe ends before the Column F Date.
4. Any timeframe that reaches this fourth rule is partially or fully on the Column F Date. The range calculation is the lesser of the Column F Date or the Finish Date Time minus the greater of the Column F Date or the Start Date Time.
I hope this helps explain it.