Timesheet calculation formula

Question

Can you help me to put formula intime sheet format for the following time sheet Start time and end time may different depend on work.

C5= Start Time(8AM) D5-E5= Lunch Break(12:00-13:00) F5-F6= Dinner Break(20:00-21:00) H5= End Time(22:00)

Normal Hours= 8 hrs (8Am to 5:00 PM) Overtime (Normal) between 17:00- 21:00 (20:00-21:00 Dinner Break) Excess Overtime= between 21:00 - 5:00AM (dinner Break 20: 00-21:00) For Example: Start Time C5 Lunch Break D5 to E6 Dinner Break F5 to G5 End H5 Answers of these calculation must come in next columns like.. Regular time 8 hrs in I5 OT between 17-21 in column J5 OT between 21-5 AM in column no.K5

Answer

First of all excuse me for the delayed reply, since there was some problem in my internet connection. Have a look in the below site for your needs. http://www.cpearson.com/Excel/overtime.htm

To Get the file for your reference download it from the below link. http://www.cpearson.com/Zips/overtime.zip