Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

get overtime working including breaks

0

hello sir/mam,

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

         sheet contains only the following details.(s.no,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,

 Amara

excel
MS Office Version: 2007
posted by: amara (rep: 2) on Mon May 29, 2017 at 7:12 am
Answer
Discuss

Answers

1
Selected Answer

Unless you work overnight, I typically like to use fractional time as decimals...so 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.

posted by: queue (rep: 467) on Tue May 30, 2017 at 10:01 am
Discuss


Answer the Question

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