Calculate Working Hours

Hi I have the following:
Task Start Date Task Finish Date Task Duration Working Hours
07/02/2008 09:00 11/02/2008 14:30 20:30:00 09:00
17:00


=IF(NETWORKDAYS(A2,B2)=1,(MIN(EndTime,MOD(B2,1))-MAX(StartTime,MOD(A2,1))),(NETWORKDAYS(A2,B2)-2)*(0.2916666666)+(EndTime-MAX(MOD(A2,1),StartTime))+MIN(MOD(B2,1),EndTime)-StartTime)


I believe the time shold be 18.5 hours as
Day 1 =7
Day 2 =7
Day 3 = weekend so 0
Day 4 = weekend so 0
Day 5 = 5.5 hours (however, 1hour for lunch between 12 and 2) so 4.5 hours.

How can I correct my formula?

Thanks

Assuming you have two additional variables StartLun and EndLun for the start and end of lunch, you could use this formula:

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

Assumes that A2 and B2 will always be within the working hours

thanks! perfect!