Calculation of date/time with range parameters??
I need some assistance writing two calculations:
In an entry I have two separate date/time fields. One for start date and time, and one for end date and time.
What I'm trying to accomplish is the calculation of time based on if the time is inside a standard time range or outside a standard time range.
IE: Time range is 7:30am  4:30pm
start date time: 12/1/2015 2:30 pm
end date time: 12/1/2015 8:00pm
Calculation 1 is for the sum of time that falls within the range = 2
Calculation 2 is for the sum of time that falls outside the range = 3.5
Is this possible?

Hi Sheena,
yes that's possible. Calculating with dates can be a bit tricky. You have to put in your timezone instead of mine ("Europe/Berlin") and if your time is UTC  x hrs, you have to change "add" to "subtract".
Here's an example for 2 calculation fields. The calculations consider every possibility: Start and End outside time range (regardless of start/end before 7:30 or after 4:30), one inside, one outside, both inside.
Field: hrs within range
var TC = parseFloat(moment(@YourStartField).tz("Europe/Berlin").format("ZZ"))/100; var start = moment(@YourStartField).add(TC,"h") var end = moment(@YourEndFeild).add(TC,"h"); var rangeStart = moment("07:30 AM","h:m a"); var rangeEnd = moment("04:30 PM","h:m a"); var hrsStart = start >= rangeStart && start <= rangeEnd ? rangeEnd.diff(start, "m")/60 : 0; var hrsEnd = end >= rangeStart && end <= rangeEnd ? rangeEnd.diff(end, "m")/60 : 0; hrsStart > hrsEnd ? hrsStart  hrsEnd : hrsEnd
Field: hrs outside range
var TC = parseFloat(moment(@YourStartField).tz("Europe/Berlin").format("ZZ"))/100; var start = moment(@YourStartField).add(TC,"h") var end = moment(@YourEndFeild).add(TC,"h"); var rangeStart = moment("7:30 am","h:m a"); var rangeEnd = moment("4:30 pm","h:m a"); var hrsStart = start < rangeStart ? rangeStart.diff(start, "m")/60 : start > rangeEnd ? start.diff(rangeEnd, "m")/60 : 0; var hrsEnd = end < rangeStart ? rangeStart.diff(end, "m")/60 : end > rangeEnd ? end.diff(rangeEnd, "m")/60 : 0; hrsEnd + hrsStart
Rainer
rg@delosconsulting.com
Please sign in to leave a comment.
Comments
2 comments