# Counting total hours at an interval basis

#### fullysic

##### New Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### AlphaFrog

##### MrExcel MVP
Put this in B2 and Drag\Copy it down column B
Code:
``=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
Code:
``=COUNTIFS('Data Source'!A:A,"<="&A2,'Data Source'!B:B,">"&A2,'Data Source'!C:C,"TRAINING")*0.5``

#### fullysic

##### New Member
Put this in B2 and Drag\Copy it down column B
Code:
``=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
Code:
``=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!

Last edited:

Replies
1
Views
302
Replies
0
Views
154
Replies
6
Views
171
Replies
0
Views
231
Replies
4
Views
152

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,689
Messages
5,833,134
Members
430,195
Latest member
AnalystStoneSteps

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back