Need help nesting a formula

[Patti]

I have a calculated field in a query (thanks NG ) to determine how much
time an employee has spent on a particular task. It subtracts the
difference between the Start and End Times, and then determines that if the
employee was on break/lunch, those times need to be subtracted as well.

Now I've added a field for [Misc Time] off the job, and need to nest that in
to the formula. I am not sure of the proper syntax, as there may be time to
subtract, or the field may be left Null.

In real life, I will know that they may be off task for 1.67 hours, so I
need to consider whether to enter in that format, or if I should convert
that to minutes before entering it. Obviously the way it is entered will
affect the structure of the formula. Any pros or cons either way?

The existing formula follows. It is very important that I not change the
basic formula. I just want expand on it. I think I should just add
"-IIf([Misc Time] is Null do nothing, else subtract [Misc Time]" but can't
get it right.

Existing formula:

Time on Job: (DateDiff("n",[Start Time],[End Time])+IIf([Start Time]>[End
Time],24*60,0)-IIf([Subtract Lunch]=True,30,0)-IIf([Subtract Break
1]=True,10,0)-IIf([Subtract Break 2]=True,10,0))/60



Thanks in advance. You guys are the best!



Patti

 

[John Spencer (MVP)]

Why not use the NZ function and subtract. Also I would enter misc time as a
number of minutes, since you see to be doing the math in minutes.

<YourCurrentFormula> -NZ([Misc Time],0)

That would subtract Misc Time unless Misc Time is null. In the latter case that
will subtract Zero.

 

[Patti]

Terrific. Thanks John!

Why not use the NZ function and subtract. Also I would enter misc time as a
number of minutes, since you see to be doing the math in minutes.

<YourCurrentFormula> -NZ([Misc Time],0)

That would subtract Misc Time unless Misc Time is null. In the latter case that
will subtract Zero.

I have a calculated field in a query (thanks NG ) to determine how much
time an employee has spent on a particular task. It subtracts the
difference between the Start and End Times, and then determines that if the
employee was on break/lunch, those times need to be subtracted as well.

Now I've added a field for [Misc Time] off the job, and need to nest that in
to the formula. I am not sure of the proper syntax, as there may be time to
subtract, or the field may be left Null.

In real life, I will know that they may be off task for 1.67 hours, so I
need to consider whether to enter in that format, or if I should convert
that to minutes before entering it. Obviously the way it is entered will
affect the structure of the formula. Any pros or cons either way?

The existing formula follows. It is very important that I not change the
basic formula. I just want expand on it. I think I should just add
"-IIf([Misc Time] is Null do nothing, else subtract [Misc Time]" but can't
get it right.

Existing formula:

Time on Job: (DateDiff("n",[Start Time],[End Time])+IIf([Start Time]>[End
Time],24*60,0)-IIf([Subtract Lunch]=True,30,0)-IIf([Subtract Break
1]=True,10,0)-IIf([Subtract Break 2]=True,10,0))/60

Thanks in advance. You guys are the best!

Patti