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.
![enter image description here](../../I/static/images/9d7b406586af2be9f360cf068d8e24f4dca96d9ed0e451472b9105babac750aa.gif)
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.
1Do you already have a sheet with the information you're looking to process? Could you provide an example of its content and layout? (Doesn't have to be real data - just close enough to give us something to work with.) – Iszi – 2014-07-17T14:58:36.753
As @gtwebb has shown it is a bit tedious but doable to chop it up within one day (24h), but you say it cab e any period - that makes it a bit harder, ;-). Might be you need to define the task better if his answer doesn't fit. – Hannu – 2014-07-17T21:45:42.960