How to divide a time period into equal periods in Excel

Solution 1:

I decided to answer this even though you should really show any attempts you've made and how it is setup.

Below is my setup. You put your From and to in columns A and B and it splits it into bins shown.

In cell C4 the formula would be:

=IF($B4>$A4,
    IF(OR(AND($A4<C$2,$B4<C$2),AND($A4>C$3,$B4>C$3)),0,MIN($B4,C$3)-MAX($A4,C$2)),
    IF(OR(AND($B4<C$2,$A4<C$2),AND($B4>C$3,$A4>C$3)),(C$3-C$2),(C$3-C$2)+(MIN($B4,C$3)-MAX($A4,C$2)))
)

Or with names for cells it would read this:

=IF(FROM>TO,
    IF(OR(AND(TO<BIN_FROM,FROM<BIN_FROM),AND(TO>BIN_TO,FROM>BIN_TO)),0,MIN(FROM,BIN_TO)-MAX(TO,BIN_FROM)),
    IF(OR(AND(FROM<BIN_FROM,TO<BIN_FROM),AND(FROM>BIN_TO,TO>BIN_TO)),(BIN_TO-BIN_FROM),(BIN_TO-BIN_FROM)+(MIN(FROM,BIN_TO)-MAX(TO,BIN_FROM)))
)

Broken down:

The first if statement checks if it happens in one 24 hour period (To is after from) otherwise it has to have wrapped around to the next day.

The second line Checks if both times are outside the bin range (either both smaller or both larger) if they are there is no data in this bin. If they aren't both outside there is data in there. By subtracting the maximum of the from values from the minimum of the to values it gives you how much time is in the bin.

The thrid line is similar to the second but it finds how much time is not in the bin and subtracts this from the full bin width.