Calculating net work hours allowing for holidays, start and finish times and lunch times

[HGSRWF]

I am trying to calculate net work hours between two dates and times using formula shown below. Complete example workbook can be downloaded here. Formula shown below correctly leaves out weekends and holidays, takes into account start and end times, but will not calculate when work time is over lunch time and deduct that time off.

<starttime,b2<a2),0,(networkdays(a2,b2,holidaylist)-(networkdays(a2,a2,holidaylist)*if(mod(a2,1)><starttime,1,(endtime-min(endtime,mod(b2,1))) (endtime-starttime))))*(endtime-starttime)*24)
<starttime,b2<a2),0,(networkdays(a2,b2,holidaylist)-(networkdays(a2,a2,holidaylist)*if(mod(a2,1)><starttime,1,(endtime-min(endtime,mod(b2,1))) (endtime-starttime))))*(endtime-starttime)*24)]
=IF(OR(EndTime<StartTime,B2<A2),0,(NETWORKDAYS(A2,B2,HolidayList)-(NETWORKDAYS(A2,A2,HolidayList)*IF(MOD(A2,1)>EndTime,1,(MAX(StartTime,MOD(A2,1))-StartTime)/(EndTime-StartTime)))-(NETWORKDAYS(B2,B2,HolidayList)*IF(MOD(B2,1)<StartTime,1,(EndTime-MIN(EndTime,MOD(B2,1)))/(EndTime-StartTime))))*(EndTime-StartTime)*24)

Details for this formula are:

1. A2 contains start date and time
2. B2 contains end date and time
3. StartTime is named range for daily start time (7:00 AM)
4. EndTime is named range for daily end time (3:30 PM)
5. HolidayList is named range for public holiday dates
6. StartTimeLunch is named range for daily lunch start time, not used in formula yet (12:00 PM)
7. EndTimeLunch is named range for daily lunch end time, not used in formula yet (12:30 PM)
8. Result shown in decimal format

</starttime,1,(endtime-min(endtime,mod(b2,1)))></starttime,b2<a2),0,(networkdays(a2,b2,holidaylist)-(networkdays(a2,a2,holidaylist)*if(mod(a2,1)></starttime,1,(endtime-min(endtime,mod(b2,1)))></starttime,b2<a2),0,(networkdays(a2,b2,holidaylist)-(networkdays(a2,a2,holidaylist)*if(mod(a2,1)>

 

[HGSRWF]

Post has truncated my formula. Please download workbook to obtain current full formula.