Counting total hours at an interval basis

[fullysic]

Hi Guys,

I have a project that i'm working on and it's giving me a bit of grief at the moment. If you can help at all it would be much appreciated!

Essentially what i'm trying to do is have chart broken into intervals throughout the day (8:00AM - 8:00PM) and be able to see is the total hours spent in various segment codes - eg. breaks, training.

I have a data source that will give the various start times and finish times for these segments, and I just need to figure out the formula to count the total hours, for each segment, for all employees at each half hour interval.

I have a sample sheet that I have created that will give you some idea. I have filled in the results that I should see in the compiler tab, and I have included sample data that is very similar to what i'm working with. If you could even point me in the right direction I would be forever greatful! Also if you would like me to elaborate on anything please let me know.

http://www.speedfile.org/283339


Thankyou in advance!

 

[AlphaFrog]

Put this in B2 and Drag\Copy it down column B

=COUNTIFS('Data Source'!A:A,"<="&A2,'Data Source'!B:B,">"&A2,'Data Source'!C:C,"BREAK")*0.5

Put this in C2 and Drag\Copy it down column C

=COUNTIFS('Data Source'!A:A,"<="&A2,'Data Source'!B:B,">"&A2,'Data Source'!C:C,"TRAINING")*0.5

 

[fullysic]

Put this in B2 and Drag\Copy it down column B

=COUNTIFS('Data Source'!A:A,"<="&A2,'Data Source'!B:B,">"&A2,'Data Source'!C:C,"BREAK")*0.5

Put this in C2 and Drag\Copy it down column C

=COUNTIFS('Data Source'!A:A,"<="&A2,'Data Source'!B:B,">"&A2,'Data Source'!C:C,"TRAINING")*0.5

Thanks so much AlphaFrog! This is exactly what I was looking for. You're a champion!