Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
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!* "dan" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
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" wrote: 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!* "dan" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
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" wrote: 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" wrote: 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!* "dan" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
DO you know where i would put in holidays in this formula?
"dan" wrote: 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" wrote: 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" wrote: 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!* "dan" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating NetworkHours
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" wrote: DO you know where i would put in holidays in this formula? "dan" wrote: 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" wrote: 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" wrote: 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!* "dan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating | Excel Worksheet Functions | |||
calculating row by row | New Users to Excel | |||
calculating the mean | Excel Worksheet Functions | |||
Calculating APY for CD | New Users to Excel | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions |