get overtime working including breaks


hello sir/mam,

         i have a doubt in calculating overtime including lunchbreaks/any other breaks.My excel

         sheet contains only the following details.(,name,dept,time in,time out,total time,over time).

           Total time will be calculated automatically,but the overtime column has the error,and the main thing is, it is provided by my teamleader who has no enter the formulas in overtime column.I've to enter myself ,so i think the formula wiil help me and i am new to excel so pls helpme at the same time i know just simple formula.

       F6=TIME IN      G6=TIMEOUT      H6=TOTALTIME      I6=OVERTIME

     H6= =IF(F6<G6,(G6-F6),($I$2-F6)+(G6-$J$3)+TEXT($I$1,"HH:MM:SS")) Here i use this formula for calculate totaltime.Exactly my question is how to calculate overtine using totaltime.(our duty time is 8:30 hrs).i hope u will help me.

Thank You forever,




Selected Answer

Unless you work overnight, I typically like to use fractional time as 8:15 am is 8.25 and 10:30 is 10.5.  Next I would set up columns with Time IN; Break/LunchTimeOut; Break/LunchTimeIn; ad Time Out (if you need Breaks AND lunch add 2 more columns).  Then you can easily make formulas to subract out 'Time off' (Breaks and/or Lunch).  We use the closest to quarter of an hour for ALL times (not 8:10, it would bump up to 8.25). Not knowing your Over time situation (some places get extra for above and beyond 8 hours) you may need to adjust, but if it is simply GREATER THAN 40 hours, then have a formula to calc amount of hours greater than 40 and multiply by the OT factor.


Answer the Question

You must create an account to use the forum. Create an Account or Login