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]

I came up with the following array formula (Press CNTL SHIFT ENTER instead of just ENTER after the formula) that sums the result of an arrayed IF statement. It appears to work with the data you provided. Format column G as [mm] to display minutes greater than 60.Sheet1

*	A	B	C	D	E	F	G
Area	Start Time	Finish Time	Duration of Event	*	Day	Total Downtime for Day
Crushing	*
Crushing	*
Crushing	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="align: right"]7/1/2013 12:34
[/TD]
[TD="align: right"]7/1/2013 12:54
[/TD]
[TD="align: right"]20
[/TD]

[TD="align: right"]7/1/2013
[/TD]
[TD="align: right"]20
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: right"]7/3/2013 15:14
[/TD]
[TD="align: right"]7/3/2013 15:45
[/TD]
[TD="align: right"]31
[/TD]

[TD="align: right"]7/2/2013
[/TD]
[TD="align: right"]00
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: right"]7/3/2013 15:56
[/TD]
[TD="align: right"]7/6/2013 17:10
[/TD]
[TD="align: right"]4394
[/TD]

[TD="align: right"]7/3/2013
[/TD]
[TD="align: right"]515
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: right"]7/4/2013
[/TD]
[TD="align: right"]1440
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

[TD="align: right"]7/5/2013
[/TD]
[TD="align: right"]1440
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="align: right"]7/6/2013
[/TD]
[TD="align: right"]1030
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 {=SUM(IF(INT($B$2:$B$4)>F2,0,IF(INT($C$2:$C$4)<f2,0,if<font color="#FF0000">($C$2:$C$4>F2+1,F2+1,$C$2:$C$4)</f2,0,if($B$2:$B$4>F2,$B$2:$B$4,F2))))} <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[MechEngSk1]

I came up with the following array formula (Press CNTL SHIFT ENTER instead of just ENTER after the formula) that sums the result of an arrayed IF statement. It appears to work with the data you provided. Format column G as [mm] to display minutes greater than 60.Sheet1

*	A	B	C	D	E	F	G
Area	Start Time	Finish Time	Duration of Event	*	Day	Total Downtime for Day
Crushing	*
Crushing	*
Crushing	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="align: right"]7/1/2013 12:34
[/TD]
[TD="align: right"]7/1/2013 12:54
[/TD]
[TD="align: right"]20
[/TD]

[TD="align: right"]7/1/2013
[/TD]
[TD="align: right"]20
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: right"]7/3/2013 15:14
[/TD]
[TD="align: right"]7/3/2013 15:45
[/TD]
[TD="align: right"]31
[/TD]

[TD="align: right"]7/2/2013
[/TD]
[TD="align: right"]00
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: right"]7/3/2013 15:56
[/TD]
[TD="align: right"]7/6/2013 17:10
[/TD]
[TD="align: right"]4394
[/TD]

[TD="align: right"]7/3/2013
[/TD]
[TD="align: right"]515
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: right"]7/4/2013
[/TD]
[TD="align: right"]1440
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

[TD="align: right"]7/5/2013
[/TD]
[TD="align: right"]1440
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="align: right"]7/6/2013
[/TD]
[TD="align: right"]1030
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 {=SUM(IF(INT($B$2:$B$4)>F2,0,IF<font color="#0000ff">(INT($C$2:$C$4)<f2,0,if<font color="#FF0000">($C$2:$C$4>F2+1,F2+1,$C$2:$C$4)F2,$B$2:$B$4,F2))))}</f2,0,if <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

Hi Mike,

You have indeed calculated the correct daily downtime.
However, when I tried to enter the formula into spread sheet, I ended up getting error.
It pints to the second last F2 in the formula.

Btw, thans for taking the time to reply to my querry. I appreciate the help.
I think it is time for me to dig into array formulas.

 

[MechEngSk1]

{=SUM(IF(INT($B$2:$B$4)>F2,0,IF<font color="#0000ff">(INT($C$2:$C$4)<f2,0,if<font color="#FF0000">($C$2:$C$4>F2+1,F2+1,$C$2:$C$4)F2,$B$2:$B$4,F2))))}</f2,0,if

Hi Mike,

I have been plyaing with it a bit more. I don't understand what does this F2,$B$2:$B$4,F2 do at the end of the formula. I get an error because it does not follow the standard if formula i.e. (condition,true,false). There is too many commas.

I have remved that string from the formula and now it seems to be calculating it correctly, however it produces the result as a date. When I change the format to [mm] it just gives me the minutes from the start of the excel date format i.e. 1/1/1900 to the date listed.

Can you please have a look at it? I'm not sure what am I doing wrong here.

Thanks,

 

[MikeWx]

It has been a while since I sent a formula with so many less than and greater than signs, and the Jeanie HTML software interprets them as control characters, so they don't appear in the formula. Here is the corrected formula. Remove the spaces before and after the less than and greater than signs. I needed to add them so you can see the entire formula on the site.

=SUM(IF(INT($B$2:$B$4)>F2,0,IF(INT($C$2:$C$4) < <f2,0,if($c$2:$c$4>F2,0,IF($C$2:$C$4 > F2+1,F2+1,$C$2:$C$4)-IF($B$2:$B$4>F2,$B$2:$B$4,F2))))

</f2,0,if($c$2:$c$4>

 

[MechEngSk1]

Thanks Mike. I'll give it try tomorrow when back at work.
I was suspecting that something must have gone missing. When I copied the formula the second condition came up with 0 where that did not appear in the forum text.

I tried to come up with an array formula in the style: - allows up to 30 arguments not only 7.
{=Sum(if(and(cond1,cond1,cond3),time,"")&if(and(cond1,cond2,cond3),time,"")&....}
The conditions worked brilliantly, and it works great if not set as array, but the moment it is run as an array it evaluates it as follows:
{=Sum(if(and({TRUE, TRUE, FALSE};{TRUE, FALSE, FALSE};{TRUE, TRUE, TRUE}),time,"")...}
So simply put, it has to meet the conditions for all the evaluated rows of data, which it will not, other wise the overall condition is FALSE. I hope you understand what I'm trying to explain. I will run with your formula, but do you know if the above method I used can be made to work? It would surely come in handy in the future. I'm not sure how to write it to evaluate the conditions not all at once but one row set at a time.

 

[MikeWx]

Did the corrected formula do what you needed? Regarding your last question, it looks like you could use a SUMPRODUCT, but I would need more information about the data you are using, the criteria, and what you expect as a result.

 

[MechEngSk1]

I'm sorry, I did not give you a feedback on the formula you have provided me. Yes it has worked briliantly. Thank you.

Well here is a bit more info that you require in regards to my last question. I was using the same data as above. This is the forumula I came up with based on the conditions listed below it. These conditions are all possible data types that can occur.

=SUM(IF(AND($B$2:$B$4>F2,$C$2:$C$4<(F2+1)),$C$2:$C$4-$B$2:$B$4,"")&IF(AND($B$2:$B$4<F2,$C$2:$C$4>F2,$C$2:$C$4<(F2+1)),$C$2:$C$4-F2,"")&IF(AND($B$2:$B$4>F2,$B$2:$B$4<(F2+1),$C$2:$C$4>(F2+1)),(F2+1)-$B$2:$B$4,"")&IF(AND($B$2:$B$4<F2,$C$2:$C$4>(F2+1)),1,"")&IF(AND($B$2:$B$4>F2,$B$2:$B$4<(F2+1),$C$2:$C$4=""),(F2+1)-$B$2:$B$4,"")&IF(AND($B$2:$B$4<F2,$C$2:$C$4=""),1,"")&IF($C$2:$C$4<F2,0,"")&IF($B$2:$B$4>(F2+1),0,""))

conditions:
1 - Start > Date, Finish < [Date+1]
2 - Start < Date, Finish > Date, Finish < [Date+1]
3 - Start > Date, Start < [Date+1], Finish > [Date+1]
4 - Start < Date, Finish > [Date+1]
5 - Start > Date, Start < [Date+1], Finish = ""
6 - Start < Date, Finish = ""

Date - callendar date for which to sum the durations

I know my formula is a bit lenghty, but it was what I could do with my knowledge. I wanted to know what am I doing wrong, since this obviously does not work in array formula, but in normal it does.

 

[MikeWx]

I modified my original formula to account for the new condition that a blank Finish Date adds to each subsequent day's downtime total indefinitely until a non-blank Finish Date is entered. I tested with a blank Finish Date for the middle row of sample data. I left the zeros in my formula, but formatted the downtime total column as [mm];; so zero totals would appear as blank. Remove blanks before the less than or greater than signs in the formula.

=SUM(IF(INT($B$2:$B$4)>F3,0,IF(ISBLANK($C$2:$C$4),F3+1-IF($B$2:$B$4>F3,$B$2:$B$4,F3),IF(INT($C$2:$C$4)<f3,0,if($c$2:$c$4> < F3,0,IF($C$2:$C$4 > F3+1,F3+1,$C$2:$C$4)-IF($B$2:$B$4>F3,$B$2:$B$4,F3)))))

</f3,0,if($c$2:$c$4>

 

[MechEngSk1]

Hi Mike,

This is awesome. It worked.
I have to study your formula a bit more to understand how it behaves. I like to understand things before I use them.

Thanks for all your help. I'll mark it solved.