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.
Solved! Go to Solution.
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))
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))
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.
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).
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))
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))
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?
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
Hi Khalil,
You would need to use datetime question to get the appropriate value, they should automatically account for the extra days.
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