I think i may have found something. If you would like to check it. U4=8:00,

U5=17:00, H1=NOW(), D2=Start Time. I also changed the format of the cells to

show [h]:mm:ss.

=MAX($U$4,MIN($U$5,MOD($H$1,1)))-MAX($U$4,MIN($U$5,MOD(D2,1)))-($U$4-$U$5)*(INT($H$1)-INT(D2))

dan said:

Well, im not working in those times these are times tickets are opened in one

of our ticketing systems. Anyway, i am wanting to see how many work hours it

takes to handle a ticket. These tickets can date way back. One of the issues

i have come across with some of the formulas i have used is that it will not

calculate past 24 hrs. This also happened int the formula you provided.

:

I believe this will work.

=(NETWORKDAYS(A2,NOW())-2)*9+17-MOD(A2,1)*24+MOD(NOW(),1)*24-8

To break-it-down:

First part of formula finds the networkdays between then and now. I subtract

two, because I will be calculating the hours specifically for those days

later on. Multiply by 9 (9 hrs per work day).

I then take amount of hours in start date, multiply by 24 (to convert to

hours) and subtract from 17 (5 o'clock). Similarly, take time now and

subtract 8 to determine how long you've already been here.

One problem though. One of your times is listed as 21:01. As this is outside

your boundary of normal work times, I do not know how you wanted to handle

that. (have no idea of how long your worked).

--

Best Regards,

Luke M

*Remember to click "yes" if this post helped you!*

:

I need some help calculating the networkhours between 2 dates. i dont want

weekends or holidays included. THe hours are from 8am-5pm. i have tried

almost every formula i can find on the internet with no luck. i have dates

starting at random times throughout the week. I am using a NOW() formula in a

cell for the end date. I want to be able to drag down the formula and

calculate the rest of the cells. Please help!

Start Time

7/7/2009 12:02

7/22/2009 9:32

7/7/2009 11:12

7/8/2009 13:01

7/7/2009 10:26

7/16/2009 13:28

7/19/2009 21:01

7/3/2009 11:41

7/8/2009 10:03