calculate time intersect between 2 time ranges

Hi

I have two sets of data in two sheets both containing time ranges (i.e start and end times). I need to be able to determine if a time range intersects the other time range and by how much and then sum the total number of minutes of that intersection. So given the following. I am writing some VBA code to transform the data and format it correctly so would prefer the answer in VBA but a cell function would work.

So given the following:

sheet1:
| A1 | B1 |
| 02:00 | 16:00 |

sheet2:
| A1 | B1 |
| 04:00 | 05:00 |

So i would want to determine the number of minutes that the sheet2 time range is within the sheet1 range.

The trouble is sheet2 time ranges could insect, overlap or be fully contained so the following examples could be possible:

1. 01:00 - 03:00 - intersect duration would be 60 minutes
2. 15:00 - 17:00 - intersect duration would be 60 minutes again
3. 01:00 - 19:00 - intersect duration would be 840 minutes
4. 12:00 - 14:00 - intersect duration would be 120 minutes

Have been racking my brains trying to find an easy way to determine this and I'm basically stuck.

Could anyone offer an insight?

Thanks
Simon

I managed to get this working.

Given A1= 08:00 and B2 = 10:00 as the time range and A2 = 07:00 and B2 = 13:00 as the time range to calculate the minutes that intersect of are full contained within row 1. I had the following formula and it seems to be correct.

=IF((B1-B2)*1440<0,IF((A1-A2)*1440>0,(B2-A2)*1440-(A1-A2)*1440,(B2-A2)*1440)+(B1-B2)*1440,IF((A1-A2)*1440>0,(B2-A2)*1440-(A1-A2)*1440,(B2-A2)*1440))

If anyone can simplify this would be great!!

Thanks
Simon

Originally Posted by kanopy123

Hi

I have two sets of data in two sheets both containing time ranges (i.e start and end times). I need to be able to determine if a time range intersects the other time range and by how much and then sum the total number of minutes of that intersection. So given the following. I am writing some VBA code to transform the data and format it correctly so would prefer the answer in VBA but a cell function would work.

So given the following:

sheet1:
| A1 | B1 |
| 02:00 | 16:00 |

sheet2:
| A1 | B1 |
| 04:00 | 05:00 |

So i would want to determine the number of minutes that the sheet2 time range is within the sheet1 range.

The trouble is sheet2 time ranges could insect, overlap or be fully contained so the following examples could be possible:

1. 01:00 - 03:00 - intersect duration would be 60 minutes
2. 15:00 - 17:00 - intersect duration would be 60 minutes again
3. 01:00 - 19:00 - intersect duration would be 840 minutes
4. 12:00 - 14:00 - intersect duration would be 120 minutes

Have been racking my brains trying to find an easy way to determine this and I'm basically stuck.

Could anyone offer an insight?

Thanks
Simon

I considered it this way. If you aranged all of the times in order, you'd need the difference between the second start time (whichever set it comes from) and the first finish time (whichever set it comes from). If this difference is positive, it means that both times started before either finished, which indicates an intersection. If this time is negative, it means that one of the times finished before the second one started, so no intersection.

A |B |C
2 |Start |End
3 |01:00 |02:00
4 |Start |End
5 |01:15 |04:00

The intersection between time B3-C3 and time B5-C5 is "=(SMALL(C3:C5,1)-LARGE(B3:B5,1))", provided that the result cell is also formatted as a time. A simple "if" or "large" cell can be used to display negative results as 0.