# Calculating the Duration/Time Elapsed between Two Form Fields

For some questionaire/survey-type scenarios, you may wish to track the time it takes the user to fill out a Form, or maybe a section in the Form. This is done by storing a corresponding start and end date/time value, with these values being set when the user answers the first and last questions in the target Form or section. Once you have the start and end date/time values, you can then use a DATEDIFF() formula function to calculate the time elapsed in your desired unit of measure.

Step-by-Step Guide
We're going to use Hidden fields for this example since we don't want the user to see these stored values. You can also use Text or Date/Time fields if you wanted to display the start and finish date/time values to the user. We're also assuming you have at least two question fields - one at the start with data name of "myfirstquestion" and one at the end named "mylastquestion."

1. Add a Hidden field with data name of "starttime" to the start of your Form design and a similar "endtime" Hidden field at the end of the Form.
2. On these Hidden fields, set the Dynamic Value to be as follows:
• starttime field: FORMAT-DATE(NOW({{myfirstquestion}}), 'yyyy-MM-dd HH:mm:ss')
• endtime field: FORMAT-DATE(NOW({{mylastquestion}}), 'yyyy-MM-dd HH:mm:ss')
• Let's explain what the above formula is doing:
• The NOW() function will return the current date/time at the time of the function being triggered
• The {{dataname}} parameter means that the NOW function will be triggered whenever an answer is set into the named field.
• The FORMAT_DATE() function is needed because Hidden fields store text values, not date values. If you didn't do this, you would likely lose the time portion of the NOW value.
• The formatting parameter ensures the date/time is captured as text like: "2016-10-21 14:10:55"
3. The final piece to this solution is to add one more field (Hidden, Text or Number) that has a Dynamic Value formula to calculate the difference between the finish and start date/time values. This field's formula would be: DATEDIFF(({{starthidden}}), {{finishhidden}}, 'SS')
• This will give you the difference in seconds between the start and end times.
• You can of course then divide this value by 60 as desired to get a minute value.
• If you wish to get the difference in minutes, hours etc, simply change the 'SS' parameter as needed.

Did you find it helpful? Yes No

Send feedback 