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
=(NETWORKDAYS(B9,D9)-1)*("17:30"-"08:30")+D9-B9-(TIME(1,0,0))
Which is the right answer.
QNS 1
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.
QNS 2
How do I get networkday to calculate through Sat to Mon and exclude Sun? Given Sat = 4 hours, Mon = 9 hours of business hours.
Thanks.
Bookmarks