How to divide a time period into equal periods in Excel

Question

I would like to find a way of dividing a period of time into 4 hourly time zones. I.e. if a piece of equipment is unavailable from 0900hrs to 1700 hrs that is 8 hours but I need to show this in 4 hourly period 00 - 0400, 0400 - 0800, 0800 - 1200 etc so that equipment is unavailable for 3 hours in the time slot 0800 - 1200, for 4 hours in the time slot 1200 - 1600 and 1 hour in the time slot 1600 - 2000. Equipment could be unavailable for any period of time over 24 hrs and at the moment I am labouriously chopping the time up manually and hope there may be an easier way.

Answer 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.

Answer 2

Here’s a solution that shows you the times within the “time zones” (bins):

       

(i.e., the equipment is unavailable from 0900 to 1200, from 1200 to 1600, and from 1600 to 1700).  The formulas in cells D5-D7 are:

D5 = =IF(AND($B5>D$2,$B5<D$3), $B5, IF(AND($B5<D$2,$B6>D$2), D$2, ""))
D6 = =IF(AND($B6>D$2,$B6<D$3), $B6, IF(AND($B5<D$3,$B6>D$3), D$3, ""))
D7 = =IF(OR(D5="",D6=""), "", D6-D5)

Explanation:

• =IF(AND($B5>D$2,$B5<D$3), $B5, … – if the From time (start time of equipment unavailability) is within this bin, then display the From time.
• … IF(AND($B5<D$2,$B6>D$2), D$2, … – if start time of this bin is within the period of equipment unavailability, then display the start time of this bin.
• … "")) – otherwise, blank.

As shown above, this doesn’t handle multi-day periods of equipment unavailability directly.  But you can get it to work by explicitly setting the To time to be in the next day: