| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating working hours to complete a task
Hi all
i have a team who respond to customer requests, and they record when the request came in and when it went out. I then report on how many working hours they took to repsond, to see if within SLA or not. To calculate this i use the following formula from Chip Pearson's website http://www.cpearson.com/excel/datetimews.htm =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) Whe StartDT The starting date and time EndDT The ending date and time DayStart The time of day that the normal work day begins DayEnd The time of day that the normal work day ends. HolidayList A range containing a list of dates to excluded, e.g., holidays or vacation days. this works very well for me, except for when the request is recieved after the DayEnd on a Friday, or comes in during Saturday or Sunday. It then calculates incorrectly. Can anyone help with this, i've been going down nested if statements to try and sort it, but suspect there must be a more logical way. regards Richard -- Richard |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating working hours to complete a task
Richard,
Not extensively tested but try this =(NETWORKDAYS(A2,B2,Holidays)-1)*($D$2-$D$1)+IF(WEEKDAY(B2,2)5,$D$2,MEDIAN(MOD(B2,1),$D$ 2,$D$1))-IF(WEEKDAY(A2,2)5,$D$1,MEDIAN(MOD(A2,1),$D$2,$D$1 )) Where D1 = workday start time D2 = workday end time A2 = task start date & time B2 = task end date and time Holidays is a named range containing holiday dates format as [hh]:mm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Richard" wrote: Hi all i have a team who respond to customer requests, and they record when the request came in and when it went out. I then report on how many working hours they took to repsond, to see if within SLA or not. To calculate this i use the following formula from Chip Pearson's website http://www.cpearson.com/excel/datetimews.htm =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) Whe StartDT The starting date and time EndDT The ending date and time DayStart The time of day that the normal work day begins DayEnd The time of day that the normal work day ends. HolidayList A range containing a list of dates to excluded, e.g., holidays or vacation days. this works very well for me, except for when the request is recieved after the DayEnd on a Friday, or comes in during Saturday or Sunday. It then calculates incorrectly. Can anyone help with this, i've been going down nested if statements to try and sort it, but suspect there must be a more logical way. regards Richard -- Richard |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating working hours to complete a task
Mike - i've run it through a few scenarios and it seemd to be the answer -
thanks so much! -- Richard "Mike H" wrote: Richard, Not extensively tested but try this =(NETWORKDAYS(A2,B2,Holidays)-1)*($D$2-$D$1)+IF(WEEKDAY(B2,2)5,$D$2,MEDIAN(MOD(B2,1),$D$ 2,$D$1))-IF(WEEKDAY(A2,2)5,$D$1,MEDIAN(MOD(A2,1),$D$2,$D$1 )) Where D1 = workday start time D2 = workday end time A2 = task start date & time B2 = task end date and time Holidays is a named range containing holiday dates format as [hh]:mm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Richard" wrote: Hi all i have a team who respond to customer requests, and they record when the request came in and when it went out. I then report on how many working hours they took to repsond, to see if within SLA or not. To calculate this i use the following formula from Chip Pearson's website http://www.cpearson.com/excel/datetimews.htm =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) Whe StartDT The starting date and time EndDT The ending date and time DayStart The time of day that the normal work day begins DayEnd The time of day that the normal work day ends. HolidayList A range containing a list of dates to excluded, e.g., holidays or vacation days. this works very well for me, except for when the request is recieved after the DayEnd on a Friday, or comes in during Saturday or Sunday. It then calculates incorrectly. Can anyone help with this, i've been going down nested if statements to try and sort it, but suspect there must be a more logical way. regards Richard -- Richard |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating working hours to complete a task
This is a very small thing, but in the formula below, someone received and
completed a task on New Years Day, which was in my holiday list. This led to them completing the task in negative hours. Is there any way of resolveing this? I can only think of cumbersome checking formula. -- Richard "Mike H" wrote: Richard, Not extensively tested but try this =(NETWORKDAYS(A2,B2,Holidays)-1)*($D$2-$D$1)+IF(WEEKDAY(B2,2)5,$D$2,MEDIAN(MOD(B2,1),$D$ 2,$D$1))-IF(WEEKDAY(A2,2)5,$D$1,MEDIAN(MOD(A2,1),$D$2,$D$1 )) Where D1 = workday start time D2 = workday end time A2 = task start date & time B2 = task end date and time Holidays is a named range containing holiday dates format as [hh]:mm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Richard" wrote: Hi all i have a team who respond to customer requests, and they record when the request came in and when it went out. I then report on how many working hours they took to repsond, to see if within SLA or not. To calculate this i use the following formula from Chip Pearson's website http://www.cpearson.com/excel/datetimews.htm =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) Whe StartDT The starting date and time EndDT The ending date and time DayStart The time of day that the normal work day begins DayEnd The time of day that the normal work day ends. HolidayList A range containing a list of dates to excluded, e.g., holidays or vacation days. this works very well for me, except for when the request is recieved after the DayEnd on a Friday, or comes in during Saturday or Sunday. It then calculates incorrectly. Can anyone help with this, i've been going down nested if statements to try and sort it, but suspect there must be a more logical way. regards Richard -- Richard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating networkdays & working hours | Excel Worksheet Functions | |||
| Format Cells to calculate hours and minutes taken to complete task | Excel Worksheet Functions | |||
| Calculating working hours | Excel Worksheet Functions | |||
| Calculating working hours | Excel Discussion (Misc queries) | |||
| Calculating working hours | Excel Discussion (Misc queries) | |||
Linear Mode
