Splitting activity duration by half an hour intervals

[rafuk73]

Hi,

Could you please help me with the following task:
have a long list of activities and their start and end time. What I need is to split the duration of each activity by half an hour intervals as my example below. The blue intervals show beginning of each interval, for example interval 08:00:00 shows half an hour after 08:00:00 and so on.

The figures in red is how I manually split the duration of each activity but would appreciate greatly if you could send me either formulas or the code which would do it automatically. Thanks a lot!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Intervals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity[/TD]
[TD]start time[/TD]
[TD]end time[/TD]
[TD]duration[/TD]
[TD]08:00:00[/TD]
[TD]08:30:00[/TD]
[TD]09:00:00[/TD]
[TD]09:30:00[/TD]
[TD]10:00:00[/TD]
[TD]10:30:00[/TD]
[TD]11:00:00[/TD]
[TD]11:30:00[/TD]
[/TR]
[TR]
[TD]Meeting[/TD]
[TD]08:24:56[/TD]
[TD]08:35:42[/TD]
[TD]00:10:10[/TD]
[TD]00:05:04[/TD]
[TD]00:05:42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]09:15:42[/TD]
[TD]09:25:58[/TD]
[TD]00:10:16[/TD]
[TD][/TD]
[TD][/TD]
[TD]00:10:16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calls[/TD]
[TD]08:25:58[/TD]
[TD]11:38:27[/TD]
[TD]03:12:29[/TD]
[TD]00:04:02[/TD]
[TD]00:30:00[/TD]
[TD]00:30:00[/TD]
[TD]00:30:00[/TD]
[TD]00:30:00[/TD]
[TD]00:30:00[/TD]
[TD]00:30:00[/TD]
[TD]00:08:27[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Pasting your table above into A1 try this in E3:

=IF(OR(E$2>$C3,E$2+"00:30"<$B3),"",MIN(E$2+"00:30",$C3)-MAX(E$2,$B3))

 

[rafuk73]

Bravo! Thank you very much!