Calculating NetworkHours

[dan]

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

 

[Luke M]

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).

 

[dan]

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.

 

[dan]

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]

DO you know where i would put in holidays in this formula?

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))

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.

 

[Luke M]

This formula is set to display hours as integers, and corrects for time
tickets entered after/before work hours:
=(NETWORKDAYS(A2,NOW())-2)*9+17-MAX(8/24,MIN(17/24,MOD(A2,1)))*24+MOD(NOW(),1)*24-8

If you want to display it using the [h]:mm:ss format, divide the whole
formula by 24.

--
Best Regards,

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

DO you know where i would put in holidays in this formula?

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))

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