If result is negative then enter 0

R

rascal

Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime to
numbers greater more than 8.
thanks
 
M

Marshall Barton

rascal said:
Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime to
numbers greater more than 8.
Click to expand...


IIf(acthrs > 8, acthrs-8, 0)
 
J

JethroUK©

assuming noone works more than 15 hrs in a shift - you could use:

[acthrs] Mod 8
 
R

rascal

Thanks everyone for your suggestions. They work beautifully.
:)
John Spencer said:
IIF([acthrs]-8<0,0,[acthrs]-8)


rascal said:
Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if
more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime
to
numbers greater more than 8.
thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Negative Numbers 2
formula for hours on time sheet 4
Conditions on form calculated textbox 3
Calculate Overtime double time 1
calculate overtime & doubletime 1
Conditional Function 2
Looking for a way to Force Successor Task to next workday IF 'x' amount of time left in workday 0
need help w/ formula for taking lunch & overtime out of a 9 hour 2

Top