Excel – How to divide a time period into equal periods in Excel

date timemicrosoft excelworksheet-function

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.

Best Answer

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

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.

Related Question