TIME CALCULATION IF/ELSE STATEMENT

Question

I have been working on creating a Work Order form for our field crews and part of it is entering their time. I have learned a lot and figured out how to do a time calculation based on the start and end time that is entered and autopopulate into another field.

And of course my dept supervisor throws a curve-ball. What I need to do (and not sure if it is passable), is:

Regular Hours Field (Calculate from the start and end time IF between a specific period of time)

ELSE, Overtime Hours Field (Calculate from the start and end time IF between a specific period time).

Example: Start time 3:00 PM End Time: 7:00 PM

Regular Hours: 2 Hours (Since end of schedule is 5pm)

OT Hours: 2 Hours (Since OT starts of 5pm).

Is this possible?

ModernElectric · Answer

@JenniferAcunto

My management team is asking that I revisit this and make improvements to my survey form and spreadsheet.

For rounding purposes, need to figure out how to write an if/else statement within my XLS Form. Have have been able to get the calculation down from start time to end time.

Here is the situation. Lets say the Hours Regular time comes out to 1.17, according to our time record standards, it would round-up to 1.25. How would I write an if/else statement for another field that said:

Timecard Hours = Hours Regular Time rounded by if/else statement. IF .17 = .25; IF .43 = .50, IF .63 = .75, and so on and so forth.

Can this be done in XLS Forms? Or if I had a table that had the conversion, could it pull the data and do a calculation and populate the Timecard Hours field?

ModernElectric · Answer

@JenniferAcunto

I appreciate your assistance on getting me started. Using ArcGIS Survey123 Connect, there are examples that have JavaScripts associated with the spreadsheets in different downloadable examples. I will do some research to see how I can learn JavaScript and write a script to tell the spreadsheet if the date is within working hours or overtime hours.

JA7 · Answer

Survey cannot pull days of the week out of a date. You will need to either include a question that says it's a weekend day to use in your calculations or you will need to move this into a javascript or post processing function. Neither of which are my forte.