nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Format time calculation with hours and minutes

bshepard

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.

Find more posts tagged with

Accepted answers

JamesTedrick

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))

startEndCalc.xlsx

image001.jpg

All comments

SueBoelk2

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.

SamanthaJordan

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?

ChrisRoberts2

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

JamesTedrick

Hi Khalil,

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

khalilramadeen

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

bshepard

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))

JamesTedrick

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).

bshepard

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.

JamesTedrick

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))

startEndCalc.xlsx

image001.jpg

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.