Number of work hours between two dates

[PJSR]

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

[Bob Phillips]

This seems to work better

=(NETWORKDAYS(C2,E2)-I2)*TIME(8,30,0)-J2*TIME(4,30,0)-MAX(0,D2-TIME(8,0,0))-MAX(0,TIME(16,30,0)-F2)

but it fails on one where the end date is a Sat. Is this possibe?

Also, it would help if you indicated which were wrong.