HELP...Time problem
Posted by Becca on June 13, 2001 7:51 AM
Trying to do a simple calulation of the average time spent per job.
In order to calulate a true average for several thousand jobs, I'm entering
the total time spent in h:mm:ss and dividing by the number of jobs.
This works until the amount of time spent has 5 digits in the hours (ie, 12926:44:00).
As soon as I have a time like that I get an #value! error in the cell where the division is done.
We have noticed that in the cells where the time has less than 5 digits in the hours,
the format is a date/time even though that's not how I set it up or how I'm entering the data
(ie, I enter 7715:46:00, but 11/16/1900 11:46:00 AM appears in the formula bar).
Help. I need this for a report due ASAP and been unable to find an answer anywhere.
Posted by Ben O. on June 13, 2001 9:00 AM
It seems that the maximum time value that Excel can store is 9999:59:59. For every time value Excel stores the year, month, day, hour, and second, every if you don't specify those things. Because of this, I would recommend using a regular number format rather than a time format.
You can convert times to regular numbers with this formula, assuming the value you want to convert is in A2:
=SUM(DAY(A2)*24,HOUR(A2),MINUTE(A2)/60,SECOND(A2)/3600)
-Ben
Posted by Becca on June 13, 2001 2:14 PM
Thanks, but this didn't seem to help. Maybe I'm doing something wrong. I entered my time into a cell and then copied your formula (changed the A2 to the correct cell)in another to convert the time to a regular number. I still got a value error.
My original division formula works with 10000+ hours when the 10000+ comes from pulling 2 or more numbers in from cells with smaller numbers.:
It seems that the maximum time value that Excel can store is 9999:59:59. For every time value Excel stores the year, month, day, hour, and second, every if you don't specify those things. Because of this, I would recommend using a regular number format rather than a time format.
