Select to view content in your preferred language

# Calculating Hours between two Time questions

3303
5
03-25-2020 04:56 PM
Deactivated User

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

Tags (4)
1 Solution

Accepted Solutions
Esri Regular Contributor

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

5 Replies
Esri Regular Contributor

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

Deactivated User

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.

Esri Regular Contributor

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

Regular Contributor

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?

Regular Contributor

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?