Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default Sum and average number of days and hours

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Sum and average number of days and hours

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")





"Basenji" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Sum and average number of days and hours

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.
--
David Biddulph


"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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 40
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number to Days Hours:Minutes David M[_2_] Excel Discussion (Misc queries) 4 February 29th 08 06:55 PM
Converting total number of hours (24 hours) into days MV Rao Excel Discussion (Misc queries) 1 January 24th 08 01:50 PM
Calculating number of hours accross days MTLeslie Excel Worksheet Functions 6 August 22nd 06 12:39 PM
Report a given number in days:hours:minutes ? TigerLord Excel Discussion (Misc queries) 2 March 26th 06 08:25 PM
Calculating number of hours between two days NMM Excel Worksheet Functions 1 October 21st 05 11:36 AM


All times are GMT +1. The time now is 06:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"