Hi,

This is longer but I think it's a lot more robust

=((NETWORKDAYS(Q838,T838)-1)*("17:30"-"8:30")+IF(WEEKDAY(T838,2)5,"17:30",MEDIAN(MOD(T8 38,1),"17:30","8:30"))-IF(WEEKDAY(Q838,2)5,"8:30",MEDIAN(MOD(Q838,1),"17 :30","8:30")))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?