Sum and average number of days and hours

[Basenji]

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.

 

[Joel]

I added 10 days 5 hours five times which should of given me 51 days 1 hour.
Instead I got 20 days 1 hour which is Feb 20,1900 (the 51st day of the year
1900 at 1:00AM)!!!!!

You can use this to get the correct results but it will be in text format
not a number.

=INT(SUM(C1:C5))&TEXT(SUM(C1:C5),":h")

 

[David Biddulph]

I would expect it to be OK, unless a cell value goes beyond 31 days in which
case the d:h format wouldn't work [so you'd need to split out the INT(cell)
for whole days and MOD(cellref,1) for the fraction].

If you've got a problem apart from that, you'll need to give us more details
of what formula you are using, what values are in the cells, what results
you are getting, and what results you expected.

One thing to check if you are getting unexpected results is whether all your
dates & times are really dates and times, or whether some contain text
instead. I see, for example that you have shown one cell as
12/8/08 15:00
and another as
12/18/08, 20:00.
By default, the latter would be treated as text (and therefore ignored by
SUM or AVERAGE functions).
Check with =ISNUMBER(cellref) and =ISTEXT(cellref).
If your subtraction of times seems to be working OK but the SUM and AVERAGE
of those calculated results aren't, then this probably isn't the problem.

 

[Fred Smith]

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]

Thanks Joel, David and Fred: the sum was more than 31 days. Changing the
formula appears to have corrected the problem.

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

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.