When the sum function is used to give the sum of days and hours and the total
exceeds 31 days it returns an invalid number.

"Jacob Skaria" wrote:

Why dont you use the custom format [h]:mm for column G. and use the normal
substraction =E1-D1 for difference and

for the total
use your formula to display in Days and hours

OR

use the SUM() function for the total..


If this post helps click Yes
---------------
Jacob Skaria


"Basenji" wrote:

In an Excel 2003 spreadsheet, column D has the start date and time; D4=
4/1/09 0:01 (24 hour time). Column E has the discontinued date and time; E4=
4/7/09 15:05 (24 hour time). Column G calculates the difference and rounds
the time up or down to the closest half hour; G4= MROUND((E4-D4)1/24). Column
G is then added to give a total of the days and hours. This formula is used,
=INT(SUM(G4:G49))&€� days, €œ&TEXT(MOD(SUM(G4:G49),1,€�h€�)&€� hours€�

It works fine except when the total number only has days and not any hours.
So if the total is 100 days and no hours, a €œvalue€� error is returned. If an
hour is added to the discontinue time it returns 100 days, 1 hour. How can
the formula be modified to show the zero hours when the column totals 100
days and no hours.

Thank you.