Calculating NetworkHours

  • Thread starter Thread starter dan
  • Start date Start date
D

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
 
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).
 
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 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))
 
DO you know where i would put in holidays in this formula?

dan said:
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.
Click to expand...
Click to expand...
 
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!*


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

dan said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top