Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total Time to Resolution
I am trying to calculate the total time it takes to resolve an issue
within an organization that has a 24 x 5 work schedule during the week and 9-5 hours on the weekend. I want to make sure that the non-work hours on the weekend are not calculated. Does anyone have a formula for the example below? Submitted Date Resolved Date 10/15/08 10:51:20 AM 10/25/08 02:54:32 PM Thanks in advance, LJ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total Time to Resolution
LJ wrote:
I am trying to calculate the total time it takes to resolve an issue within an organization that has a 24 x 5 work schedule during the week and 9-5 hours on the weekend. I want to make sure that the non-work hours on the weekend are not calculated. Does anyone have a formula for the example below? Submitted Date Resolved Date 10/15/08 10:51:20 AM 10/25/08 02:54:32 PM Thanks in advance, LJ Submitted Date in A2, Resolved Date in B2 =NETWORKDAYS(A2,B2)-IF(WEEKDAY(A2,2)<6,MOD(A2,1),0)-IF(WEEKDAY(B2,2)<6,1-MOD(B2,1),0)+(((INT(B2)-INT(A2)+1)-NETWORKDAYS(A2,B2))*8-IF(WEEKDAY(A2,2)5,(MOD(A2,1)-TIME(9,0,0))*24,0)-IF(WEEKDAY(B2,2)5,(TIME(17,0,0)-MOD(B2,1))*24,0))/24 Not sure if this is optimized, but it seems to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting Dates to get total time work time excluding weekends | Excel Discussion (Misc queries) | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
Track time to resolution (without including after hours on weekdays and weekends) | Excel Worksheet Functions | |||
Grand total time between two total cells | Excel Discussion (Misc queries) | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |