DateTime calculation returns incorrect result- but only sometimes.

[JulietK]

Hi I am having an issue where incorrect calculation is given from a 'time elapsed' calculation, but only sometimes. 

I've got two DateTime fields, first field as start time and second field as end time. I've used decimal date calculation formula for elapsed time int((decimal-date-time(${endtime}) - decimal-date-time(${starttime})) * 24*60) to give the answer in a separate viewable field.

The calculation formula works correctly most of the time - but sometimes, the formula returns an answer minus 1, as you can see in the images below.

<<< Incorrect, should be returning 3

                                                                  Correct >>>

<<< Correct 

                       Incorrect, should be returning 8 >>>

There don't seem to be any specific pattern to which elapsed minutes result will return an incorrect result. For example, with start time 12:00pm, incorrect results were returned for 3, 8, 11 etc minutes elapsed but when start time was 12:01pm, different numbers of minutes elapsed returned incorrect results. Has anyone had an issue like this?

Additional question - I have a DateTime field set to display today() as default and 'Required'. However I would like to have the time section display as blank (it displays defaults of 12:00), to force all respondents to manually enter in the time (their time entries will never be now(), or able to be calculated) and minimise any possibility of accidentally not entering time data or respondents being lazy. Is this possible? I would rather retain the DateTime and not have a separate Time field.

Thanks!

[Jim-Moore · Accepted solution]

Hi @JulietK 

The issue you're seeing is likely due to rounding (one minute in decimal time is 0.00069444444444). The int() function converts a decimal to a whole number by discarding the decimal places (it doesn't apply any rounding). Try replacing the int() function with round(), for example:

round((decimal-date-time(${endtime}) - decimal-date-time(${starttime})) *24 *60,0)

There's more on dates and times in Survey123 in this blog post and also more decimal time here.

Regarding your second question, it's not possible to display the time as blank in a dateTime question if that question has a calculation or default. Without a calculation or default, both the date and time inputs will be empty until the user interacts with the question.

Hope this helps. Best,

Jim

[JulietK]

Hi Jim, perfect thank you so much for the solution! I will try the round function. 

I had read all the relevant blog posts and articles from ESRI as well as trawled through the questions pages, but had not been able to find any mention of this problem (or the possibility of it occurring) anywhere, let alone a solution.

Thank you for your response re: the blank time field, very helpful to have this confirmed! Any idea if there are any workarounds to make separate Date and Time fields appear as compact as DateTime in the web form? I understand web form does not support theme-grid unfortunately, but anything to help condense the relatively large empty space taken up by having separate Date and Time fields would be amazing.

[JulietK]

Your formula with the round() with ,0 added at the end works perfectly! Thank you so much 🙂