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.)
Hi,
First of all i would like to appreciate you all for doing such a great work and having each others back, So thank you all for your time and efforts.
My name is Nasr, and I'm in need of a formula to calculate overtime as mentioned in the attached table.
* In our shop overtime is stated to calculated after 3 hours of duty.
* Overtime will be calculated in hours, minutes will not be considered at all.
Hi Nasr
Please see the attached revision to your sheet which shows this siolution.
An example of the formula to use (for the rules in your question/sheet) is this (e.g. in cell D4):
=MAX(0,ROUNDDOWN(MOD(B4-A4,1)*24,0)-3)The key bit (in bold above) calculates the time difference between finish and start times AND will work over midnight- see how it works via Don's excellent tutorial Calculate Hours Worked - Day or Night Shift & Breaks in Excel. That also shows you how to allow for breaks - essentially using the same formula to calculate the time on breaks then subtracting it from the whole shift.
Around that is the ROUNDDOWN function, ROUNDDOWN(<<shift time>>0), removing the minutes by rounding to 0 decimal places. I subtract 3 from that but that could produce a negative number so around that I add the MAX function with 0 as the other argument- that means the overtime is the maximum of zero or the subtracted time (i.e. if the time subtraction is less than zero, it will show zero, if more than 3 hours were worked, it will be the number of whole hours overtime).
Note that the cells using this formula should be formatted as a number (not General)
Hope this is what you need.