Sum and average number of days and hours
Thanks Joel, David and Fred: the sum was more than 31 days. Changing the
formula appears to have corrected the problem.
"Fred Smith" wrote:
I suspect your problem is that your results are more than 31 days. Excel
can't display more than 31 days using date format. Unfortunately, there's no
equivalent [d] format, like there is an [h] format.
If you're trying to display more than 31 days, you need something like:
=int(sum(a1:a5))&" days, "&text(mod(sum(a1:a5),1),"h")&" hours"
Regards,
Fred
"Basenji" wrote in message
...
I have a column of start days and times, 12/8/08 15:00, and end days and
times, 12/18/08, 20:00. I subtract the two columns to get the number of
days
and hours between the two times, which is formatted with a "d:h" custom
format to show the rounded off number of days and hours, 10 D 5 H. This
column of time differences needs to be added and averaged. The sum
function
does not show a correct total nor dos the average function show a correct
average. Is it a function issue or a formatting issue? The total for the
hours and the average of the time differences should be in a day hours
format, d:h, or some format that will show the total or average days and
hours. I have experimented with a varity of formats and tried different
functions but have been so far unsuccessful. Any assistance would be
appreciated. Thank you.
|