Excel formula to calculate amount of overtime

Question

I need help calculating the number of hours over 40. Its a payroll spreadsheet and I want to calculate overtime.

If Q4 is greater than 40 hours then subtract 40 hours from Q4 and display the number of hours difference. If cell is less than 40 hours, return 0.

Here's what I have:

=IF(Q4>40,Q4-40,0)

But when calculated against a cell containing 49:23 it always returns 00:00

The source cell is custom formatted [HH]:MM because it wouldn't display hours over 12 when calculating the cells to get the total hours.

Answer 1

Excel stores times (and dates) as days. The display format chosen has no effect on that (and is not related to your issue).

You need to do your math in days, not in what you get displayed. If you want to check for "> 40 hours", check for "> 40/24 days".

That would be =IF(Q4>40/24,Q4-40/24,0) for your example.

Answer 2

It seems that the value in cell Q4 is a DateTime. In Excel, this is stored as a number where the integer part is the number of days and the fractional part is the time of day. To get the number of hours instead of the number of days you need to multiply by 24. The following formula will get you the number of hours in excess of 40.

=IF(Q4*24>40,Q4*24-40,0)

If Q4 contains 49:23, this formula returns 9.3833. Make sure that the cell containing the hours of overtime is formatted as a number (or "General") rather than a time.

If you want to ignore fractions of an hour, use the INT function:

=IF(Q4*24>40,INT(Q4*24-40),0)

As Scott points out in his comment (thanks Scott), repeating the Q4*24-40 in the formula is inelegant and can lead to errors if the formula needs to be changed. A better version of the first formula would be:

=MAX(Q4*24-40,0)

and a better version of the second would be:

=MAX(INT(Q4*24-40),0)

These work because any time Q4*24 is less than 40, Q4*24-10 will be less than zero and the MAX function will then return 0 instead of Q4*24-40.