Create Pivot to show total time by interval

[furrypotato]

SampleData.xls

I have a large list of all meetings. Each row details the type of meeting, date and start and finish time of meeting. Each row would be for an individual person.

I need to create a table to show the total time spent in each type of meeting code during each 15 minute interval through the day.

So, the column headers would be the start time of each 15 minute interval ( 08:00, 08:15, 08:30, 08:45 etc)
The Row's would be each of the meeting codes ( HR, Quality, Finance etc)

So far, I've done it a horrible slow way without pivot tables. Firstly I added an extra column for each 15 minute interval and put formulas in there that totalled the time spent in each interval for that row's meeting. ( So a meeting that was 08:20 - 08:40 would have 10 mins in the 08:15 interval and 10 minutes in the 08:30 interval )

I then manually created a table and placed formulas in each to obtain the total time for each meeting code during each interval.
Messy, long winded and slow. I seriously need to learn some pivot table skills !


So my request - Is it possible to do it all using a pivot table in one go ?
Or even if I still keep the first stage of my current method so that I have a large calculated table with each interval as columns, and every meeting in a row and then pivot that ?

The attached sample data shows a small example of the raw data.

[AlKey]

I created an additional column (E) to get the time between Start and End.

Please see attache file with the PivotTable.

[furrypotato]

Thanks for the fast reply.

I may not have explained this quite correctly.

I've attached another file to show a better idea of the output table I needed.
The difference is that I need it to be set intervals as column fields, each 15 minutes through the day.
examplereult.xls

[AlKey]

The PivotTable cannot show the data that doesn't exist. If nothing happened at 8:00 the PivotTable has nothing to display.

[furrypotato]

Apologies, I guess you've partly answered it for me as i know that pivot wont work from my original data.

I've now attached a new version of my data where I've used some formulas to get me part way there. It now has the data sorted into the amount of minutes in each interval already.

So now I need to go from the source data on the 'Data' sheet of attached workbook, to create a pivot table to look like what I have in the 'Result' sheet.

Thanks again for continued help !SampleData2.xlsx

[AlKey]

I would advice to use the data layout that was suggest previously.

Please see attached file with formula solution.