IIF Loop

[Eranka]

Hi

IIf((IIf(((Sum([Actual_Hours]))*24<20),(Sum([Actual_Hours])),CStr((Int(Sum([Actual_Hours])*24) & ":" & Round(((Sum([Actual_Hours])*24)-Int(Sum([Actual_Hours])*24))*60,1)))))>=20,20,(IIf(((Sum([Actual_Hours]))*24<20),(Sum([Actual_Hours])),CStr((Int(Sum([Actual_Hours])*24) & ":" & Round(((Sum([Actual_Hours])*24)-Int(Sum([Actual_Hours])*24))*60,1))))))

Is the above code correct? if not what is the correct way to use?

[pbaldy]

If it works it's correct, if not then it isn't.

sorry, couldn't resist. I'm not on a computer, but it looks like you're trying to format a time interval? That formula is difficult to read, understand, and debug. I'd create a VBA function that took the interval as an input and returned the desired result. It would be easier to follow and you'd be able to call it from anywhere.

[Eranka]

Hi

what i want to do is, if sum of no_of_hours is greater than 20 hours then show result as 20 else show sum of no_of_hours

[pbaldy]

That appears to be doing more than that, but if just that it's simply:

IIf(Sum([Actual_Hours])> 20, 20, Sum([Actual_Hours]))