Expert: Bill - 2/20/2006

Question
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
Using Excel 2000
I have a cell with a volume value 51676 units
I have a cell with a value of 5800 units/hr.
I have a cell with a start time of =Time(3,0)
I would like to have a cell with the end time.
The equation would be start time plus the amount of time to complete 51676 units (8.9 hr.)
The cell result should show 11:54AM
Cell B31 is 51676
Cell B32 is =Time(3,0)
Cell B33 is =(B32)+(B31/5800)
Value in cell is 12:50AM
Thank You
Al
Answer -
Well, ummmmm, OK.  So, what is your question?

Ok, The question is, What do I do to get the correct answer in the cell?
Thanks

Answer -
Where do you get 5800 or what does the 5800 mean in the formaula?


=(B32)+(B31/5800)

I apologize for not being clear.
The 5800 is units per hour.
The question is, I have a machine that runs 5800 units per hour and I have 51676 units to run and the machine starts at 3:00 AM. What time does the machine complete the run? The correct answer is 11.54PM, but excel says 12:50AM.
Answer -
"...Cell B32 is =Time(3,0)..."

B32 can not have =Time(3,0).  That is an invalid formula because the seconds are missing and EXCEL will not let you enter it.  The correct syntax is =Time(3,0,0).  

And do you mean for this be 3 HOURS or 3:00 A.M.?  Entering it as =Time(3,0,0), when enrered correctly, will be 3:00 A.M.

Thank you for your help but, I still have a problem.
Cell B32 was =Time(3,0,) and Excel allowed it to be entered it also returned 3:00AM to the cell. I corrected it to =Time(3,0,0). After I made the correction,  Cell B33 is =(B32)+(B31/5800)and still returns 12:50AM.
Obviously the above formula is incorrect.
What would the correct formula be to show the ending time with a:
Start time of 3:00AM
Have a run rate of 5800 units per hour
Have 51,676 units to complete.
Thank you
Al  

Answer
I am still confused as to how entering =TIME(3,0) works since all versions of EXCEL have to have all three time elements (hours, minutes, seconds) in the =TIME function to return a valid time.  Regardless, though, that is not your problem.

You have to convert 8.90965517241379 (what you get when you use the formula =B31/5800) to a "TIME" number.  The 8.90965517241379 is 8 hours and then 9/10 of an hour.  The 9/10 of an hour needs to be converted to what time that is.  Time is based on a base 6 sysyem (60 seconds in a minute) and not a base 10 system).  So, the 8 hours is the number of hours but that has to be added to the number that represents the 9/10 of an hour also.

So, add B32 to the integer portion of the formula =B31/5800 (whose answer is 8.90965517241379) and then add to that the 9/10 of an hour AFTER that has been converted to a time.

The formula below basically says

=TIME(8,57,0) after all of the internal functions have ben calculated.  Make sense?


=B32+TIME(INT(B35),INT(ROUND(MOD(B35,INT(B35))*60,2)),0)