Formatting Formula Results

[Guest]

Here's what i'm looking at....



Col C Col D Col E
Row 9 08-28-06 2:28 PM 08-28-06 3:25 PM 0 days 0.95 hours

Col E is Derrived from this code -->
=IF(D9<>FALSE,IF(AND(INT(C9)=INT(D9),NOT(ISNA(MATCH(INT(C9),
HolidayList,0)))),"0 days 0 hours", IF(INT(C9)=INT(D9), "0 days " &
ROUND(24*(D9-C9),2)&" hours ", MAX(NETWORKDAYS(C9+1,D9-1,HolidayList),0)+
INT(24*(((D9-INT(D9))-(C9-INT(C9)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart)))& " days
"&MOD(ROUND(((24*(D9-INT(D9)))-24*DayStart)+
(24*DayEnd-(24*(C9-INT(C9)))),2), ROUND((24*(DayEnd-DayStart)),2))&" hours
")),"")


I need Col E to be formatted like this..... 0 Days 9 Hours 16 Minutes


I keep trying to set the format of the cells by using this.... d" Days "h"
Hours "mm" Minutes"


What am I doing wrong and why is it showing decimal values???


I'm also trying to add the total of every tenth row by using this....


=IF(E9<>"",SUM(E9:E18),"") but for some reason it won't add, I keep getting
zeros.


Please help.
Thanks in Advance,
Rob

 

[Guest]

If you have a cell containing the value:
1.23456
as a time it is actually a little more than one day. Format the cell as:
Custom dd" days ":hh" hours ":mm" minutes "
and you will see:
01 days :05 hours :37 minutes