Exclude overnight hours in DateDiff calculation

[MikhaylaB]

I've written an Arcade attribute rule to calculate hours spent working on a project. I need to update that script to exclude the hours between 4:01PM and 6:59AM. My code is as follows:

var StartDate = $feature.TimeStart
var EndDate = $feature.TimeEnd
var TimeDiff = DateDiff(EndDate, StartDate, 'hours')
return TimeDiff

Link to previous post:

https://community.esri.com/t5/arcgis-pro-questions/update-integer-field-with-datediff/td-p/1388053#M...

[KenBuja · Accepted solution]

This should work. It calculates the number of days between the start date and end date and subtracts 15 hours for each day.

var StartDate = $feature.TimeStart
var EndDate = $feature.TimeEnd
var TimeDiff = DateDiff(EndDate, StartDate, 'hours')
var DayDiff = DateDiff(DateOnly(EndDate), DateOnly((StartDate)), 'days')
var HoursOff = 15 * DayDiff

return TimeDiff - HoursOff

[MikhaylaB]

Thanks, that should be good. Might you know how to include hours and minutes, like 2.5 hours? I noticed it rounds down to the closest hour.

[KenBuja]

If you want the hours just rounded to something like 2.5, use Round function (and template literals)

return `${Round(TimeDiff - HoursOff, 1)} hours`

You can add this to return the hours and minutes

var TotalTime = TimeDiff - HoursOff
var Hours = Floor(TotalTime);
var Minutes = Floor((TotalTime - Hours) * 60)
return `${Hours} hours and ${Minutes} minutes`

[MikhaylaB]

Hi, I have a question unrelated to this post, but you were very helpful with my last Arcade question. I have two attribute rules that I need to combine, because they are firing in the wrong order as separate rules. I need to first calculate the GPM from a value entered into the Flow field (both are fields in the same related table). I need that GPM value to update a parent feature class in the "class" field. Do you have an idea how I could combine these two expressions?

//Calculate GPM

var flow = $feature["Flow"];
var gpm = 0;

if (!isEmpty(flow)) {
gpm = (29.7 * (2.5 * 2.5) * Sqrt(flow) * 0.9)
}
return gpm;

//Update Parent Class

var parent_id = $feature.hydrantObjid ;

var parent_class = FeatureSetByName($datastore, "hydrantCopy", ["OBJECTID", 'class'], false);
var parent_records = Filter(parent_class, "OBJECTID = @parent_id");

var updates = [];
var i = 0;
var new_value = 'no gpm';

if ($feature.GPM >= 1500) {
new_value = "Blue - 1500 GPM or Greater";
}
else if ($feature.GPM >= 1000) {
new_value = "Green - 1000-1499 GPM";
}
else if ($feature.GPM >= 500){
new_value = "Orange - 500-999 GPM";
}
else {
new_value = "Red - 0-499 GPM";
}

for (var row in parent_records) {
// If the parent row is null or has a different value, updated it
if (IsEmpty(row['class']) || row['class'] != new_value)
{
updates[i++] = {
'OBJECTID': parent_id,
'attributes': {"class": new_value}
};
}
}

return {
'result': parent_id,
'edit': [
{'className': 'hydrantCopy',
'updates': updates
}
]
};

[MikhaylaB · Accepted solution]

I ultimately didn't need to exclude the overnight hours because I learned the work stops by end of business hours and a new entry is created for the next day. I did have an issue with my field not allowing decimals as a result from my equation, so I changed from a short integer to a double. I wanted hour and fraction of an hour as a whole number + decimal. Final code below:

var StartDate = $feature.TimeStart
var EndDate = $feature.TimeEnd
var TimeDiff = DateDiff(EndDate, StartDate, 'hours')
var Minutes = DateDiff(EndDate, StartDate, 'minutes')
var MinDiff = (Minutes-60)/60
var HourMin= Number(TimeDiff + MinDiff)
return TimeDiff