PJSR
09-13-2007, 02:18 AM
Hi,
I need to calculate turn around time between two sets of data reflecting both a starting date and time and obviously an ending date and time. This should reflect work hours meaning a day that starts at 08:00 and end at 16:30.
That would be a start and would be great if someone can show me how to calculate this.
But obviously people don't work on Sundays and only work from 08:00 to 12:00 on Saturdays... Can't even imagine how one would take this into account.
Can someone please help me with this? Even just the first solution would be great.
I attached an example of the data.
I tried the following formula but it gave inconsistant results:
This formula, using NETWORKDAYS function from Analysis ToolPak add-in will give you the total hours worked Monday to Friday, assuming the start and end dates are always between 08:00 and 16:30 Monday to Friday, in G2
=(NETWORKDAYS(C2,E2)-1)*17/48+F2-D2
you could then count the Saturdays in the range and add 4 hours for each which would then make the formula
=(NETWORKDAYS(C2,E2)-1)*17/48+F2-D2+INT((WEEKDAY(C2)+E2-C2)/7)/6
format result as [h]:mm
I need to calculate turn around time between two sets of data reflecting both a starting date and time and obviously an ending date and time. This should reflect work hours meaning a day that starts at 08:00 and end at 16:30.
That would be a start and would be great if someone can show me how to calculate this.
But obviously people don't work on Sundays and only work from 08:00 to 12:00 on Saturdays... Can't even imagine how one would take this into account.
Can someone please help me with this? Even just the first solution would be great.
I attached an example of the data.
I tried the following formula but it gave inconsistant results:
This formula, using NETWORKDAYS function from Analysis ToolPak add-in will give you the total hours worked Monday to Friday, assuming the start and end dates are always between 08:00 and 16:30 Monday to Friday, in G2
=(NETWORKDAYS(C2,E2)-1)*17/48+F2-D2
you could then count the Saturdays in the range and add 4 hours for each which would then make the formula
=(NETWORKDAYS(C2,E2)-1)*17/48+F2-D2+INT((WEEKDAY(C2)+E2-C2)/7)/6
format result as [h]:mm