On Wed, 4 Mar 2009 08:13:02 -0800, Lori B
wrote:

Here is an example. Cell d3 I have 02/02/2009 9:54. In cell D2 I have
02/09/2009 13:50. I have the time split out from each of those cells into
cell H2 (13:50) & h3 (9:54). My formula is
=networkdays(d3,d2,Lookup!$D:$D)-1+(h2-h3). I don't want to count the first
day so I have -1 in my formula to not include that. My results show in the
format of d:h:mm and for my example above would be 5:3:56. 5 days, 3 hours,
& 56 minutes. I use this to calculate the amount of time a job was in a
department. I then want to add up the total time of all jobs in that one
department. Does this help? Maybe I should be using a different format to
display the amount of time calculated per job? Thanks for your help

That clarifies things.

As far as I know, you won't be able to have both the format you want, and also
be able to use the result for math operations.

I would suggest that you either express your result as days and fraction of a
day, perhaps with a limited number of decimal places, or use a separate column
to display the results as text.

Your formula, in days, and fractions of a day, shows: 5.163888889

If that value is in, for example, G3, then:

=INT(G3)&TEXT(MOD(G3,1),":h:m")

would display your result in your preferred format. But you'll only be able to
do math operations on the value in G3.

You could also display the result as:

=INT(G3) & TEXT(MOD(G3,1),""" days, ""h"" hours, ""m"" minutes""")

which would be unambiguous to the casual perusor of the sheet.
--ron