Lets try this again, submited this post 10 minutes ago and havn't been able to find it. It did prompt me to login when I clicked preview post so maybe it bugged some home. Very happy I was able to regain this post via the back browser button though :P

Hello there, first time reader first time poster. My company has set the task in my hands of comming up with a time managment workbook. So far I've made a workbook for each month of the year. Contained in each of the workbooks is an individual sheet for each week that tracks each employee's hours based on his clock in/clock out time. Also, there are sheets for each day that tracks what time they were called in for work, their load time, and the time they were told to go home.

Taking the information that was inputed we calculate the their pre-trip time, which is the time it takes for them to clock in to the time they are loaded, as well as the cleanup time, the time from when they are told to go home till they actually clock out. At the end of the workbook I have a sheet that processes all this information. Giving you an avg Call in, avg Clock In, Clock in Low, Clock in High, Avg Pre-Trip, Pre-Trip Low, Pre-Trip High, avg In-Yard, avg Clock Out, Clock Out Low, Clock Out High, avg Cleanup, Cleanup Low, Cleanup High.

All of the numbers we input into our system are in 1/100ths of an hour. So 7:30 is actually 7.50, and 6:40 is actually 6.67. So the code for say the Average Call in looks like
=(MID(AVERAGE($'Friday Week 4'.H7;$'Thursday Week 4'.H7;$'Wednesday Week 4'.H7;$'Tuesday Week 4'.H7;$'Monday Week 4'.H7;$'Friday Week 3'.H7;$'Thursday Week 3'.H7;$'Wednesday Week 3'.H7;$'Tuesday Week 3'.H7;$'Monday Week 3'.H7;$'Friday Week 2'.H7;$'Thursday Week 2'.H7;$'Wednesday Week 2'.H7;$'Tuesday Week 2'.H7;$'Monday Week 2'.H7;$'Friday Week 1'.H7;$'Thursday Week 1'.H7;$'Wednesday Week 1'.H7;$'Tuesday Week 1'.H7;$'Monday Week 1'.H7);3;2)*0.6)+MID(AVERAGE($'Friday Week 4'.H7;$'Thursday Week 4'.H7;$'Wednesday Week 4'.H7;$'Tuesday Week 4'.H7;$'Monday Week 4'.H7;$'Friday Week 3'.H7;$'Thursday Week 3'.H7;$'Wednesday Week 3'.H7;$'Tuesday Week 3'.H7;$'Monday Week 3'.H7;$'Friday Week 2'.H7;$'Thursday Week 2'.H7;$'Wednesday Week 2'.H7;$'Tuesday Week 2'.H7;$'Monday Week 2'.H7;$'Friday Week 1'.H7;$'Thursday Week 1'.H7;$'Wednesday Week 1'.H7;$'Tuesday Week 1'.H7;$'Monday Week 1'.H7);1;2)
But unforunately the upper management doesn't want to convert each number from 1/100ths to 1/60ths so I used the mid function to determine what the last 2 digits would be and multiplied them by 0.6 to convert them to 1/60ths then did another mid search for the first 2 numbers and added them together.

Now problem number one I'm having comes up when either the last digit is a 0 such as 12.50 or 11.00. Excel doesn't seem to recognize a 0 as a digit when it comes to the mid function and skips right over it thus when I do (mid(A3;3;2)*.6) *where A3 = 12.00* It returns Err:502 because excel won't multiple zeros. Im considering adding a fake value such as .005 to each value so that there is something to mutiply the decimal place by then rounding it up to the nearest 1/100th, and then subtracting that .005 out after the equation is completed. Not sure if there is an easier route to go to find the mid of a list of cells where the average number has a zero in the end.

Next problem with such forumal is quite the reverse, when the first two digits are not two digits. Such as 9.50, since my mid function starts at the first digit which is 9 and counts 2 integers over it gives me 9.5. I've tried to add a leading zero to each cell and again Excel doesn't read zeros.

Now each of those work beautifully if there are two whole digits before and after the decimal place. They convert from 1/100th of an hour to 1/60th of an hour. Again managment doesn't want to convert each of the numbers to 1/60th on their own *lazy bums.*

Next up we have the entire reason for this drawn out thread. The Min and Max values for the data. This is the code for determing the Max value for a drivers Pre-Trip Time and rounding that value to the nearest minute.
=MAX($'Friday Week 4'.G7;$'Thursday Week 4'.G7;$'Wednesday Week 4'.G7;$'Tuesday Week 4'.G7;$'Monday Week 4'.G7;$'Friday Week 3'.G7;$'Thursday Week 3'.G7;$'Wednesday Week 3'.G7;$'Tuesday Week 3'.G7;$'Monday Week 3'.G7;$'Friday Week 2'.G7;$'Wednesday Week 2'.G7;$'Tuesday Week 2'.G7;$'Monday Week 2'.G7;$'Friday Week 1'.G7;$'Thursday Week 1'.G7;$'Wednesday Week 1'.G7;$'Tuesday Week 1'.G7;$'Monday Week 1'.G7)
Now what I'm trying to do is to nock out any statistical anomalies. Such as a rogue value of 350 minutes *because of canceled orders, truck broken ect* while the rest of the values are an average of 10-30 minutes. Having that one value of 350 minutes mis-represents his longest time because it is an statistical anomalie. I'm trying to figure out *with no such luck* how to rule out the lowest and the highest values to get a "true average."

Any help would be greatly appreciated in this matter. If you need further explanation please ask and I'd be happy to further explain the workbook.

Thanks,
Matt