NewDeal Hot Tip 1008

NewCalc Overtime Values

How do I get NewCalc to deduct a half hour lunch after calculating the difference between two cells with my start and end times, then figure my overtime hours over 8.5 (0.5 for lunch) at time-and-a-half in another column?

Answer:

The Condition is usually a logical comparison. The true result is a formula (or value) that will execute if the Condition is true. The false result will execute otherwise. The false result must be at least a 0 (zero) or "" (null string) depending how it will be used in subsequent calculations.

Here's one approach to the whole time sheet:

• Column A for date:
  Enter YRMODA as 6 digits (up to the year 2000); or use the =DATE(Year,Month,Day) formula, and set the Number Format for a date.
• Column B for entry-time:
  Use fixed integer for Number Format and enter the times in military 24-hour fashion: 0930 for 9:30 AM, 1430 for 2:30 PM.
• Column C is exit-time, using the same format as column B.
• Column D interprets B as minutes:
  Assuming the row is 4, the formula is =INT(B4/100)*60 +MOD(B4,100)
  This converts the hour:minute value into pure minutes.
• Column E: same for C
  Just copy your D4 formula and paste it in E4.
• Column F: is lunch time off, in minutes.
  I originally avoided this, putting an automatic 30-minute debit into the time calculation; but that COSTS a worker 30-minutes pay each day (s)he's not on the job!
• Column G: Performs the math, even for overnight shifts.
  This is the formula =IF(E4<D4,(E4-D4-F4+1440)/60,(E4-D4-F4)/60)
  That adds a day (1440 minutes) if the out-time is less than the in-time, does the math straight otherwise, and converts the result in each case from minutes to fractional hours.
• Column H: Does the straight time up to 8 hours.
  The formula is =IF(G4<8,G4*$J$1,8*$J$1)
  This is assuming that the hourly rate is posted in cell J1. The $J$1 fixes the reference so the formula will always refer to that cell.
• Column I: Does the overtime:
  The formula is =IF(8<G4,(G4-8)*$J$1*1.5,0)
  This gives time-and-a-half for the hours over 8.
  This can be combined with the formula in the previous column to do the total in one cell, but perhaps you'd like to see the overtime separately. If not, in Column H you could use =IF(G4<8,G4*$J$1),8*$J$1+(G4-8)*$J$1*1.5)
• Column J in the format totals the straight time and overtime pay:
  The formula is =H4+I4 (that's i4, not fourteen).
• The last three columns can be set to a Number Format for currency.

Now that you have a working row, it can be copied (select a4 to I4) and pasted into whatever row-range you select.

If you don't want to see the minutes-columns, change their cell width to 0 points after copying and pasting the formulas.

Last Modified 28 Feb 1999