Select to view content in your preferred language

ROUND DECIMAL VALUE UP OR DOWN

386
9
07-31-2024 01:19 PM
Frequent Contributor

Good Afternoon

I have a decimal field that is created from calculating the total time. IE 7:00 AM to 7:45 AM = 0.75

What I am trying to do figure out is additional rounding.

If the field \$regular_hours_1 = 0.18 I need to write a formula that calculates \$regular_hours_round_1 = 0.25

Not sure the proper way to do this, either a rounding formula or an if/else statement.

Between 0.00 - 0.12 = Round to 0.00

Between 0.13 - 0.37 = Round to 0.25

Between 0.38 - 0.62 = Round to 0.50

Between 0.63 - 0.87 = Round to 0.75

Between 0.88 - 0.99 = Round to 1.00

I have gotten this far on doing the time calculations, its this part that I cannot figure out.

Appreciate it

9 Replies
MVP Esteemed Contributor

For Arcade, use When | Logical functions | ArcGIS Arcade | Esri Developer:

``````var regular_hours_1 = 0.18;

return when(
regular_hours_1 < 0.13, 0.0,
regular_hours_1 < 0.38, 0.25,
regular_hours_1 < 0.63, 0.5,
regular_hours_1 < 0.88, 0.75,
1.0
)``````
by
MVP Frequent Contributor

Honestly, doesn't sound like you are rounding. It appears you just want to output quarter hours. In that case, using IF() statements should suffice.

E.g. Something like this:

``if(\${regular_hours_1}>=10,substr(\${regular_hours_1},2,string-length(\${regular_hours_1})),if(\${regular_hours_1}<10,substr(\${regular_hours_1},1,string-length(\${regular_hours_1})),0))``
``if(\${regular_hours_1_dec}>0 and \${regular_hours_1_dec}<=0.12,0,if(\${regular_hours_1_dec}>0.12 and \${regular_hours_1_dec}<=0.37,0.25,if(\${regular_hours_1_dec}>0.37 and \${regular_hours_1_dec}<=0.62,0.5,if(\${regular_hours_1_dec}>0.62 and \${regular_hours_1_dec}<=0.87,0.75,if(\${regular_hours_1_dec}>0.87 and \${regular_hours_1_dec}<=0.99,1,0)))))``

A quick test:

Frequent Contributor

I think this should do the trick. This shortcut inside the form will assist our crews from having to do the math.

Thank You

by
MVP Frequent Contributor

Glad this seems to be working for you.

Just wanted to toss this out there: If you are showing the calculations in text fields, you may wish to set those text fields to read only = yes to avoid users making unintended edits or breaking calculations.

Also, on the off-chance that this applies to your business case, I should mention that in my quick workflow mock-up, I didn't account for scenarios where hours were in the triple digits. For example, 143.34 hours will not work properly with my posted formula. A quick tweak will fix that though. I mention this since you seem to be working with "OT Hours", which may reach into the triple digits depending on your business case.

Good luck!

Frequent Contributor

Thank you for the information. I have been working on nothing but this updated form for the past few days, sending updates to my management team and receiving design feedback from them.

The overtime should rarely, if ever, reach the triple digits. However, I will make a note to address this issue.

Let me ask this (based on the design feedback from my management team):

The user selects the date and time in the form. The new request for the design is to have the rounding happen at the start time and end time and show up as a decimal. IE: Time called 7/31/2024 @ 1:13 PM = 13.25 and End Time 7/31/2024 @ 5:40 PM = 17.75

I have an idea on how I can accomplish this, but wondering if you have a trick?

Goal is for the user to select and data/time and have everything calculated for them. Hope this makes sense what I am trying to get to.

Overtime Hours Worked auto-completed based on the entered start and end time rounded from the starting/ending times.

by
MVP Frequent Contributor

No tricks as far as I am aware. Basically, you'd:

1. Extract the time from the dateTime questions.
2. Use the above process to convert the user-entered times into your quarter hours.
3. Use your two new quarter hour times and calculate the difference between them, giving you your hours worked.
Frequent Contributor

Thank you for the advice. I believe I have figure out a good workflow for the form and to make it not too difficult for the user adding their time entry.

Side Question: I am using Survey123 Connect and building the form via Excel (XLS Form). Is it possible to add a table as a note to the form thru the XLS form?

by
MVP Frequent Contributor

By 'add a table', do you mean like an image to help the user, like the below example?

If so, that is quite easy. Just add the image file to your media folder, and reference it like so:

Just be aware that the image is caps-sensitive. So be sure to copy-paste the full name (including file extension).

Frequent Contributor

Exactly

Yup, that is very easy.

Thank You