Counting total hours at an interval basis

fullysic

New Member
Joined
Aug 9, 2010
Messages
20
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!
 

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
Joined
Sep 2, 2009
Messages
16,450
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
Joined
Aug 9, 2010
Messages
20
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:
Master Excel Bundle

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

Threads
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?

Disable AdBlock

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
Top