topic Re: Calculating Hours between two Time questions in ArcGIS Survey123 Questions

Calculating Hours between two Time questions

BenVan_Kesteren1 — Wed, 25 Mar 2020 23:56:41 GMT

Hey,

I am trying to wrap my head around calculating hours between two TIME fields. There are alot of blog posts around this, but none that I have found that give me hours worked in this format:

Start Time = 10am

Finish Time = 2.30pm

Calculation == 4.5

I have the below excel document, can someone please tell me how to get the result I am looking form?

I know this is a simple one but I can't get it!

See row 12 & 13 below

I try doing ${time_end}-${time_start}/3600000 but I get this error:

Note: I know there is a reference error on line 13 in the screenshot, it has been resolved, it is unrelated to this question

Re: Calculating Hours between two Time questions

Jim-Moore — Thu, 26 Mar 2020 01:40:09 GMT

Hi Ben

The trick is to convert the time questions to decimal time. So your calculation would look something like:

round((decimal-time(${time_end}) - decimal-time(${time_start})) * 24,1)

Note that the web app supports decimal time only, while the field app supports Epoch time and decimal time. This Help topic gives a good comparison of the two storage methods: Decimal date time—Survey123 for ArcGIS | Documentation

And this blog post covers all the ins and outs of working with dates and times: https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis

As mentioned in the blog, there's a handy Dates & Time sample survey available in Connect which has plenty of good examples. Also see the comments in the blog post regarding issues with rounding.

Hope this helps

Cheers,

Jim

Re: Calculating Hours between two Time questions

BenVan_Kesteren1 — Sun, 12 Dec 2021 08:35:27 GMT

Jim that works perfectly, exactly answers my question. I will now refer to the supplied literature to build my knowledge around this, as it may answer my follow up question, which is;

The brief of this project has changed now to allow for people to register a shift which could occur over many days.

E.g If I worked from 2100 Saturday night until 0300 Sunday morning, that's a 6 hour shift I need to capture. So I have just changed my start/stop fields to be date/time, and updated my calculation according to the above hyperlink you provided:

round((decimal-date-time(${shift_end}) - decimal-date-time(${shift_start})) * 24,1)
‍

Works perfectly, thanks again for the excellent information you provided above.

Re: Calculating Hours between two Time questions

Jim-Moore — Sun, 29 Mar 2020 23:23:44 GMT

Cheers Ben, glad to hear it's working well!

Re: Calculating Hours between two Time questions

ChristopherMask — Wed, 18 Oct 2023 17:25:00 GMT

Question,

I can get this to work 12:00 PM- 1:23 PM:

round((decimal-date-time(${OthReleaseTime}) - decimal-date-time(${DispatchedDateTime})) * 24,1)

Returns a value = 1

but I don't want it to round.

For example 8:18 PM - 4:45 PM = 3.55

Is this possible?

Re: Calculating Hours between two Time questions

ChristopherMask — Wed, 18 Oct 2023 19:45:48 GMT

Question,

I can get this to work 12:00 PM- 1:23 PM:

round((decimal-date-time(${OthReleaseTime}) - decimal-date-time(${DispatchedDateTime})) * 24,1)

Returns a value = 1

but I don't want it to round.

For example 8:18 PM - 4:45 PM = 3.55

Is this possible?