Hi

 

 I have a timesheet database and sometimes the post call field might result in a negative result. This however means that the Total overtime field will take it off the calculation as it includes the result of 4 fields. Is there a way to make it so the total calculation field doesn't include the post field if it is a negative. In the file attached you can see that the overtime should calculate 6 and half hours (pre, camera and broken added together are 6.5 hrs but it is taking 30 mins off cos the post is a negative .

 

The post is a negative because it is taking the time from the wrap out time to the leave time less 45 mins we give free. But when you add in the camera overtime  in this instance it thinks it is a negative 30 mins cos it is less then the free 45 mins we give.

 

Is what I am asking possible? I tried adding something like this to the post field at the end of the calculation

 Let( [ MyCalcResult = Post Call ]; Max(MyCalcResult;0) )

but then it stopped calculating and also then in turn stopped the total overtime from working

username is admin and password is pirates.

 

thanks

Sarah 

 

 

 

Edited August 7, 20178 yr by littlered136

Do you want the Post Call field to return 0 when the current calculation would have returned a negative number? Or do you want it to remain negative and only be excluded from the Total Overtime calc? 

Hi

I would like it to show a 0 ideally but happy to condition format it to be set to white text when it is less then a 0 if it is not possible to do both within the calculation. 

Would this work for you:

Let ( 
postcall = Leave - timesheets::wrap - ( Camera + 2700 ) 
; 
If (
Leave and postcall > 0 ; Ceiling ( postcall / 1800 ) * 1800 
)
)

Note: you must be careful not to name your variables with a name already used as a field name - otherwise you might get unexpected results.

 

Hi

Yes it has thank you! and thanks for the advice on the variables names.