Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number to Days Hours:Minutes | Excel Discussion (Misc queries) | |||
Converting total number of hours (24 hours) into days | Excel Discussion (Misc queries) | |||
Calculating number of hours accross days | Excel Worksheet Functions | |||
Report a given number in days:hours:minutes ? | Excel Discussion (Misc queries) | |||
Calculating number of hours between two days | Excel Worksheet Functions |