Formulas

[stevewebrg]

Hello

I have made a daily timesheet in LC designer, and would like help on how to get the formulas doing what I want. I'm new to this, and barely know anything about scripting or anything like that, so the simpler the solution, the better.

Attatched is the pdf of the timesheet. How it works, is the user picks an activity from the drop down list, enters any details that are neccessary, and then enters the times that they start and finish the activity. What I want it to do is calculate (from the start and finish times) the total hours spent (in HH:MM) in the total column. I've fiddled around for hours trying to achieve this, but I can't seem to get my head around it.

I'd appreciate anyone's helpful input.

Thanks

[Niall_O_Donovan · Accepted solution]

Hi,

I would change the start time and finish time fields to "Date/Time fields" and then in the object/binding tab change from Date to Time. Also set up a time pattern for the inputted time in the object/field tab.

I have put some Formcalc in the first row "total hours" field.

if (StartTime == null or FinishTime == null) then  //check if either start time or finish time is null
    $ = null
elseif (StartTime > FinishTime) then //check if finish time is before start time
    xfa.host.messageBox("Check start and finish times. Re-input correct times", "Daily Time Sheet Alert", 0)
    StartTime = null //optional, you/user might not like data disappearing
    FinishTime = null
else
    $ = (Time2Num(FinishTime, "HH:MM") - Time2Num(StartTime, "HH:MM")) / (60 * 60 * 1000) // convert from milliseconds 
endif

There are test you can do for the characters that the user can input, to make sure that the user is complying with the correct time format (for the formula to work properly). Someone else might be able to help you with that.

Once you get the first row working the way you want, you can copy that down to the other rows.

I have saved the form as dynamic as well.

Also I would put in tooltips to help the user. Lastly a sum function at the end could total the hours.

Hope that helps,

Niall

[stevewebrg]

Thanks Niall, that does indeed help.

I'd appreciate if someone could help me with that - it sounds interesting!