numbers function IF greater than using date & time

Question

Level 1

0 points

numbers function IF greater than using date & time

I am trying to produce a number sheet that will allow me calculate a working rota for staff.

The problem i am having is when i try and use a IF function to remove 1hr (for lunch) if the person works 8 hours or more.

If that person works between 6 and 8 they require a 30 mins lunch.

I have already setup the sheet to calculate the total shift time by subtracting the starting time from the finishing time. i.e. 18:00 - 08:00 = 10hrs

Now i need it to subtract 1hr for lunch.

It should read something like IF 10 >8 then -1 from 10.

I just don't know how to not cause an error.

ideally i would like it to be able to handle 2 IF's in the one cell. if its 6-8hrs - 30mins or if its greater than 7 -1 hr.

I currently have all the cells set at time and date.

Any help would be greatly appreciated.

Richard

MacBook Pro with Retina display, OS X Yosemite (10.10)

Posted on Nov 8, 2014 3:15 PM

Reply

Answer 1

Nov 8, 2014 3:40 PM in response to ricspratt

Ric,

Let's say your gross hours worked is in Column D. In another column, you can write an expression for the net hours.

Net Column Expression: =D-IF(D>6, .5, 0)-IF(D>8, .5, 0)

That will take off a half hour after 6 hours and another half hour after 8 hours. I assume that's the way it works. If not, further explanation may be required.

Jerry

Reply

Answer 2

SGIII

Level 8

37,562 points

Nov 8, 2014 8:38 PM in response to ricspratt

If you haven't already, have a look at the Employee Schedule template (File > New and look under the Business section of the template chooser). Note that it includes a lunch break. It may provide you with good ideas you can apply to your document.

SG

Reply