CODEWITHSUNDEEP
excel
F37 President
F37 President

Reputation: 11

How to sum the number of overlapping time frames in excel

Hoping someone can help,

I work for a fire department and i am trying to determine the number of times all our rigs on the road at emergencies at the same time. I have all the data from date, times, etc... So what i am looking for is an excel summation total that would display the sum of overlapping times that were greater than 3. So kinda as follows:

Rig  Date      Start Time  End Time  
1    1/1/2015     0703       0759    
2    1/1/2015     0705       0823    
3    1/1/2015     0706       0815    
4    1/1/2015     0723       0759    
1    1/1/2015     0802       0845

With more than three rigs on the road after 0723 it would grant me a total of one but then at 0802 rig 1 goes back out again meaning my total would increase by 2 and so on and so on. I dont have the slightest clue as to how to program this. I have three years of data i need to crunch through and something like this would help me greatly. Any help whatsoever is appreciated. Thanks in advance and lets see what you all come up with!

Upvotes: 1

Views: 3324

Answers (1)

Aprillion
Aprillion

Reputation: 22340

First, you might want to convert the times to Excel date-times (assuming you already applied
Format as Table to your data, so all new formulas get populated for all rows automatically):

  • if the values are already Excel times, use:

    =$B2 + C2

  • if you have integers like "703" (formatted as "0703"), use:

    =$B2 + INT(C2/100)/24 + MOD(C2, 100)/24/60

  • or if you have values-as-text:

    =$B2 + LEFT(C2, 2)/24 + RIGHT(C2, 2)/24/60

Don't forget to format the new columns using Custom Number format d.m. hh:ss or m/d hh:ss.

The next step is to count all ongoing deployments that end only after the current deployment started, i.e. use following formula as illustrated on screenshot:

=COUNTIFS(F$2:F2, ">" & E2)

excel screenshot

Please make sure that the formula e.g. in row 13 looks like =COUNTIFS(F$2:F13, ">" & E13) to check that you are on the right track. Also none of the values can be 5 or more if you only have 4 rigs, otherwise you have an error in your data.

And to count number of times when all 4 rigs were in use, the formula looks like this:

=COUNTIFS($G$2:$G$13, 4)

In case you also want to sum the time while none rigs were available, add 2 more columns in your table. Column H in my illustration needs following array formula (entered by Ctrl+Shift+Enter):

=IF(G2=4, MIN(IF(F$2:F2 > E2, F$2:F2)), "")

And a normal formula in column I:

=IF(G2=4, H2-E2, "")

Don't forget to format numbers accordingly.

And do a simple sum: =SUM($I$2:$I$13).

Upvotes: 2

Related Questions