topic Re: Format time calculation with hours and minutes in ArcGIS Survey123 Questions

Format time calculation with hours and minutes

bshepard — Thu, 09 Nov 2017 15:58:19 GMT

Is there an easy way to calculate a duration from start to end displayed in hours and minutes? I've only seen mention of either/or. I've tried a few things unsuccessfully. Essentially I just want ${EndTime} - ${StartTime} in a h:mm format. So, if I have StartTime = 12:00PM and EndTime = 2:06PM, I want Duration to be 2:06, not 2.1 hours or 126 minutes.

Re: Format time calculation with hours and minutes

JamesTedrick — Thu, 09 Nov 2017 19:23:23 GMT

Hi Brian,

This is pretty straightforward if a little tedious. First to note is that time is stored internally in the Survey123 app in milliseconds (ms). 1000 ms = 1 sec = 1/60 min = 1/3600 hr. This leads to:

# of hours = time elapsed / (3600*1000) > take the integer of the result

# of minutes (total) = time elapsed / (60*1000) > then use the modulo operation with 60 (mod 60) to get the number of minutes under a whole hour

A small thing to remember visually is to pad a zero if the number of minutes is less than 10

The Excel sheet attached steps through the formulas; all together the function (assuming the end question is t2 and the start question is t1:

concat(int((${t2} - ${t1}) div (1000*60*60)), ":", if(int(((${t2} - ${t1}) div (1000*60)) mod 60) < 10,"0",""), int(((${t2} - ${t1}) div (1000*60)) mod 60))

Re: Format time calculation with hours and minutes

bshepard — Thu, 09 Nov 2017 21:41:27 GMT

Thanks for you reply James. After having time to work through your spreadsheet, I realized that the calculations were step wise and not additive. The single calculation on the last row is all I needed. I appreciate your help.

Re: Format time calculation with hours and minutes

JamesTedrick — Fri, 10 Nov 2017 18:19:12 GMT

No worries. If you happen to be on UTC time, this can be simplified to t2-t1 - that represents the total number of milliseconds since midnight UTC and can be used in the format-date function (you could then add/subtract the UTC offset, though this would break for times lasting longer than 24 hours).

Re: Format time calculation with hours and minutes

bshepard — Mon, 05 Mar 2018 19:19:59 GMT

I just updated my calculations to use Decimal Time, based on the blog post here: https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis. Occasionally the calculation is 1 minute off. When there's a 1 hour difference the Unix Time calculation correctly shows 1 hour, the Decimal Time calculation shows 59 minutes. Any suggestions?

Here's my new calculation: concat(int((decimal-date-time(${EventEndTime}) - decimal-date-time(${EventStartTime})) * 24), ":", if(int((((decimal-date-time(${EventEndTime}) - decimal-date-time(${EventStartTime})) * (24*60))) mod 60) < 10,"0",""), int(((decimal-date-time(${EventEndTime}) - decimal-date-time(${EventStartTime})) * (24*60)) mod 60))

Re: Format time calculation with hours and minutes

bshepard — Fri, 16 Mar 2018 22:02:26 GMT

Moving the hour/minute calculations into the start and end time fixed the issue. Thanks for the suggestion at the Dev Summit, James.

concat(int((decimal-date-time(${EventEndTime} * 24) - decimal-date-time(${EventStartTime} * 24))), ":", if(int((((decimal-date-time(${EventEndTime} * 24 * 60) - decimal-date-time(${EventStartTime} * 24 * 60)))) mod 60) < 10,"0",""), int(((decimal-date-time(${EventEndTime} * 24 * 60) - decimal-date-time(${EventStartTime} * 24 * 60))) mod 60))

Re: Format time calculation with hours and minutes

khalilramadeen — Thu, 08 Aug 2019 11:53:24 GMT

Hello James Tedrick‌

Is it possible to calculate the hours and minutes for more than 24 hours. I tried the excel sheet you've attached here, but when fill for example (2:47 PM as start and 12:32 AM as end) this will give a minus value. I wanted to know if it's possible to get values more than 24 like 50:45 as summation.

Thanks in advance,

Khalil

Re: Format time calculation with hours and minutes

JamesTedrick — Fri, 09 Aug 2019 16:54:43 GMT

Hi Khalil,

You would need to use datetime question to get the appropriate value, they should automatically account for the extra days.

Re: Format time calculation with hours and minutes

ChrisRoberts2 — Tue, 03 Sep 2019 04:32:13 GMT

This was just what I was looking for! Thanks James!

Just one question how do I get it to calculate part hours eg 1.5? this just seems to give the answer in whole hours

Re: Format time calculation with hours and minutes

Anonymous User — Fri, 20 Nov 2020 16:47:15 GMT

I've followed this format and this process works in the Connect app but when testing this from a browser I get a NaN:NaN for the result, which I believe is "not a number". I'm not sure what I'm missing as in the sample spreadsheet those fields were all notes, so where is it getting confused in the process that the result is not a number? Or why is it looking for a number if the field is a note/string?

Re: Format time calculation with hours and minutes

SamanthaJordan — Thu, 06 Apr 2023 13:04:30 GMT

Did you solve why it did that for the browser only? I am having the same issue?

Re: Format time calculation with hours and minutes

SamanthaJordan — Thu, 06 Apr 2023 13:06:33 GMT

Hello James. Just like the anon below, I used the same syntax you provided, and it works great on the survey 123 app but not on the browser form. I need it to work on the browser form because that is how we plan to distribute our form. Do you know what I can do to make it work for browser?

Re: Format time calculation with hours and minutes

SueBoelk2 — Tue, 25 Feb 2025 18:20:23 GMT

Hi James-

I am a real rookie using Survey 123 and especially the calculations (but I am not giving up)!

I used your formula to calculate and output hours and minutes:

concat(int((${t2} - ${t1}) div (1000*60*60)), ":", if(int(((${t2} - ${t1}) div (1000*60)) mod 60) < 10,"0",""), int(((${t2} - ${t1}) div (1000*60)) mod 60))

However, I don't know how to show the colon between the hours and minutes after running the form.

Thanks.