IIF Statement syntax?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked.

I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried

IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0

Can anyone give me some help on what I've done wrong? I just get #NAME in the box

Thanks for the help in advance

Mac
 
Could it be that IFF should be IF?
Bernard

mac said:
I have a timesheet template that allows a user to enter their Time In,
Click to expand...
Time out for Lunch, Time back In, Time Out for the day, then calculates the
time differences to give total number of hours worked.
I am trying to make it so that if the total time for the day is Negative,
Click to expand...
it assigns zero as the default. Here is the statement that I tried.
 
Mac,

IIF is VB, Just use IF

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

mac said:
I have a timesheet template that allows a user to enter their Time In,
Click to expand...
Time out for Lunch, Time back In, Time Out for the day, then calculates the
time differences to give total number of hours worked.
I am trying to make it so that if the total time for the day is Negative,
Click to expand...
it assigns zero as the default. Here is the statement that I tried.
 
Unless I am reading it wrong, as well as using IF instead of IIF, you will
need to change the formula to either:

=IF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, 0,ROUND((((E7-E6)+(E4-E3))*24),2))
or
=IF(ROUND((((E7-E6)+(E4-E3))*24),2)>0, ROUND((((E7-E6)+(E4-E3))*24),2),0)

in order to return positive numbers. You could also consider:

=Max(ROUND((((E7-E6)+(E4-E3))*24),2),0)

but how are you ever going to end up with a negative time for the day? By
going over midnight?

Regards

Sandy


--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


mac said:
I have a timesheet template that allows a user to enter their Time In,
Click to expand...
Time out for Lunch, Time back In, Time Out for the day, then calculates the
time differences to give total number of hours worked.
I am trying to make it so that if the total time for the day is Negative,
Click to expand...
it assigns zero as the default. Here is the statement that I tried.
 
Back
Top