Calculating working hours to complete a task

R

Richard

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

Where:

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
 
M

Mike H

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

Richard

Mike - i've run it through a few scenarios and it seemd to be the answer -
thanks so much!
--
Richard


Mike H said:
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 said:
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))))))

Where:

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
Click to expand...
Click to expand...
 
R

Richard

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

Where:

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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating working hours between 2 dates 7
MS Excel Function - Networkdays 3
Calculating the difference between hours 1
Getting and error while calculate business hours between 2 dates with holidays. 4
Elapsed Time 1
Calculating the difference between hours 1
Missing lunch!!! 1
dates and times in excel 6

Top