Adding Days and Hours
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.
|