Hi below is a sample of how my worksheet look like.

Starting from B1 cell

Start date Start time End date End time
8/5/2010 9:00 8/5/2010 14:00
8/7/2010 9:00 8/9/2010 15:00

Working Day Mon to Fri 0830 to 1730 Lunch hr 1
Working Day Sat 0830 to 1230 Lunch hr 0

Start date Day 1 End date Day 2 Elapse Time Total Hrs
8/5/10 9:00 Thu 8/5/10 14:00 Fri 4:00
8/7/10 9:00 Sat 8/9/10 15:00 Mon 2.21

The formula that i using on Elapse Time are


Which is the right answer.


But there is an scenario where employee tend to end work before lunch hour. So in this case, is there a formula to work with above formula that indicate different lunch hour whereby if employee end work before lunch or start work after lunch hour. The minus 1 hour will not be calculated.


How do I get networkday to calculate through Sat to Mon and exclude Sun? Given Sat = 4 hours, Mon = 9 hours of business hours.