TIME CALCULATION IF/ELSE STATEMENT

[ModernElectric]

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?

[JenniferAcunto]

I think the easiest way to do this in the form would be to include a dateTime field indicating the start of OT. The today() function sets the time to noon, so we can add 5 hours to that to get the OT start time of 5 pm. 

date-time(decimal-date-time(today()) +  0.2083334)

Then we can use this date field to calculate our regular hours. 

round(((decimal-date-time(${otstart}) - decimal-date-time(${startdate})) * 24),2)

And also our OT hours.

round(((decimal-date-time(${enddate}) - decimal-date-time(${otstart})) * 24),2)

[ModernElectric]

@JenniferAcunto 

Jen: Thank You!! Looks like you are understanding exactly what I am trying to accomplish. I was able to take your example and build a test and it does exactly as you illustrated.

If we could, and escalate it just a little?

Our field crew's working hours are Monday to Friday from 7:30 AM to 4:00 PM PST. Anything outside of those hours and on weekends is all considered automatic overtime. 

In order for this form, and future forms to be more user friendly, we want them to be able to enter their start time and end time and allow the formulas to do the calculations for them to determine their regular hours and if it during off-time, their OT hours calculated. 

Appreciate your ongoing assistance

[JenniferAcunto]

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. 

[ModernElectric]

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

[ModernElectric]

@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?