Skip to main content
aakashv3100
November 23, 2022
Question

How to calculate Business Working duration in seconds between a date time range for a specific calendar?

  • November 23, 2022
  • 0 replies
  • 0 views

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.