Networkdays

[Kolacube]

=(NETWORKDAYS(A2,B2)-1)*(EndTime-StartTime+StartLun-EndLun)+MOD(B2,1)-MOD(A2,1)+((MOD(B2,1)<StartLun)-(MOD(A2,1)<StartLun))*(EndLun-StartLun)

The above formula assumes that A2 and B2 will always be within the working hours, as the start time and end time are between 9-5 (Normal working hours), how would i get this formula to tell me

1. add the time on start or finish if task start time is before 9 or after 5, or if the finish time is before 9 or after 5
2. Add the time if the task starts on a weekend and finishes on a weekend

Many thanks if you can help me out
PS StartLun and EndLun is between 12-1PM

[daddylonglegs]

This formula will only count business hours [those hours between StartTime & StartLun and EndLun & EndTime Monday to Friday] even if A2 or B2 is at the weekend, in the evening (or early morning) or within lunch break

=(NETWORKDAYS(A2,B2)-1)*(EndTime-StartTime+StartLun-EndLun)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1), EndTime,StartTime)-MEDIAN(MOD(B2,1), EndLun,StartLun),EndTime-EndLun)-IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1), EndTime,StartTime)-MEDIAN(MOD(A2,1), EndLun,StartLun),StartTime-StartLun)

[Kolacube]

thanks ever so!