Calculate time by time periods splitting productivity

production01.png

I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.

Example

If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time

I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time . Any ideas ?

I am David taking on Goliath

Thanks

Excel 2007

Hi and welcome to the forum

Not sure if you know this or not, but excel treats time (real time) as a decimal ** 1 (day), so 06:00am is actually 0.25, 12 noon is 0.5 etc. And a date is just a number that represents how many days have passed since 1/1/1900. With that in mind, see if you can make use ** ths "cheap and nasty" sample that I threw together for another member....

Also Please do not upload a picture ** your file...rather, upload a sample ** your workbook, showing what data you are working with, a few samples ** your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

First of all , my apologies.

I have made several novice mistakes. I am using Excel 2007 with a reference book for excel 2013, and will be converting data to a 2010 format once at work.

The excel sample is now formatted where its more understandable.

Problem:
I am evaluating efficiency times based on room utilization in three time blocks in minutes: 0730-1530,1531-1730,1731-close. After searching the threads I have been able to provide utilization in minutes based on a reference time table. I know I can use a logic equation to determine if the station was even open in each time block.

My problem is that on a single unit the utilization will vary each day. There will be up to 5 different utilization points that will need to be calculated. This makes my arrays extremely vulnerable to errors. I can go in each day and make changes but would like to come up with an automation.

The first hurdle is being able to find data clusters on a specific day to review utilization, and the ability to look at a range of dates for weekly utilization. The problem becomes more complex when this has to be done over 11 units.

IS excel capable of doing this kind of complex calculations ?

Reference File
Book3.xlsx

Thanks again for the direction I should go