Question

Help with timesheet formulas please

Forms

Updated May 20, 2020

asked on May 20, 2020

I found several posts with examples but I'm struggling. Just a simple formula to start with...so I copied the following from another post and I'm getting an error; I replaced the original formula's variables with the two variables I'm testing in my form, Timesheet.Time_in and Timesheet.vFinal_out.

=IF((AND(INDEX(Timesheet.Time_in, ROW())<>"",INDEX(Timesheet.vFinal_out, ROW())<>"")), ROUND(((DATEVALUE(INDEX(Timesheet.vFinal_out, ROW()))+TIMEVALUE(INDEX(Timesheet.vFinal_out, ROW())))-(DATEVALUE(INDEX(Timesheet.Time_in, ROW()))+TIMEVALUE(INDEX(Timesheet.Time_in, ROW()))))*24,2), "")

Here's a screen shot of the error I'm seeing:

0 0

Replies

replied on May 20, 2020

Try to break the calculation down into chunks. Start with the inner most parentheses and do a sub-calculation in a different field on just that. Then use that variable to build the next formula. It's easier to get something like this working by growing it one piece at a time.

1 0

replied on May 20, 2020

LF follows the order of Multiplication, Division, Addition and Subtraction when computing formulas

I would recommend you use SUM,SUB,MULT and DIV to create groups within your formula which I find works for me as I build out the individual sections of the Formula and then pull them together as Jared mentions

In your current formula, from what I could quickly see, there are issues with the bracketing which is likely causing your issue.

If you want to provide the verbal logic to your requirement it could help to understand

0 0

replied on May 20, 2020

I found the problem; the example I was using had a field set as a Date with the "Show Time" option checked, whereas I was using a straight Time data type. I switched it to Date with "Show Time" enabled, and now the formula works.

Thanks for your responses.

0 0

You are not allowed to follow up in this post.

Question

Question

Help with timesheet formulas please

Replies

Sign in to reply to this post.