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?
An IF formula uses the following syntax:
=IF(Condition,true result,false result)
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
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.
Return to Index
Last Modified 28 Feb 1999