Question
How to calculate Business Working duration in seconds between a date time range for a specific calendar?
Hi,
I have written down a code to calculate this, please let me know if it can be more refined.
a!localVariables(
/*rule inputs local!StartDate, local!EndDate*/
local!StartDate:todate(ri!StartDate),
local!EndDate:todate(ri!EndDate),
local!WorkStartTime:time(2,0,0,0),
local!WorkEndTime:time(10,0,0,0),
local!StartTime1:totime(ri!StartDate),
local!EndTime1:totime(ri!EndDate),
local!StartTime2:if(local!StartTime1local!WorkEndTime, local!WorkEndTime, local!EndTime1),
local!EndTime: if(local!EndTime2local!WorkEndTime,local!WorkEndTime,local!StartTime2),
local!StartDateTime:datetime(year(local!StartDate), month(local!StartDate), day(local!StartDate), hour(local!StartTime),
minute(local!StartTime),second(local!StartTime),milli(local!StartTime)),
local!EndDateTime:datetime(year(local!EndDate), month(local!EndDate), day(local!EndDate), hour(local!EndTime),
minute(local!EndTime),second(local!EndTime),milli(local!EndTime)),
local!ElapsedDays:elapseddays(
local!StartDateTime,
local!EndDateTime
),
sum(
a!forEach(
items: if(
local!ElapsedDays<1,
{0},
enumerate(local!ElapsedDays)
),
expression: a!localVariables(
local!CurrentStartDate:if(
fv!isFirst,
local!StartDateTime,
adddays(datetime(year(local!StartDate), month(local!StartDate), day(local!StartDate), hour(local!WorkStartTime),
minute(local!WorkStartTime),second(local!WorkStartTime),milli(local!WorkStartTime)), fv!item)
),
local!CurrentEndDate:if(
fv!isFirst,
local!EndDateTime,
adddays(datetime(year(local!StartDate), month(local!StartDate), day(local!StartDate), hour(local!WorkEndTime),
minute(local!WorkEndTime),second(local!WorkEndTime),milli(local!WorkEndTime)), fv!item)
),
if(
calisworkday(local!CurrentStartDate, "YOUR_CALENDAR_NAME"),
elapsedseconds(
local!CurrentStartDate,
local!CurrentEndDate
),
0
)
)
)
)
)
Currently, the business hours are hard coded, unable to fetch the working hours for each date from calendar, but yeah this code still kind of solves my problem.
