Re: How to calculate the number of hours between date/time fields, excluding weekends

Hi George,

Here my formula , Field type is numeric but I want to calculate exact hours

Note: business hours excluding it is fine

var timeofday DayStartTime = ToTimeOfDay("12:00");
var timeofday DayEndTime = ToTimeOfDay("21:00");
var datetime StartClock = [Start Date];
var datetime EndClock = [Completed Date];
var datetime OnHoldStartClock = [On Hold Start Date];
var datetime OnHoldStopClock = [On Hold Stop Date];

var DateTime StartDateTime = Max($StartClock, ToTimestamp(ToDate($StartClock), $DayStartTime));
 
var DateTime EndDateTimeTesting = If(IsNull($EndClock), Now() ,$EndClock);

var DateTime EndDateTime = Min($EndDateTimeTesting, ToTimestamp(ToDate($EndDateTimeTesting), $DayEndTime));

var DateTime OnHoldStarDateTimeTesting = If(IsNull($OnHoldStartClock), Now() ,$OnHoldStartClock);

var DateTime OnHoldStarDateTime = Max($OnHoldStarDateTimeTesting, ToTimestamp(ToDate($OnHoldStarDateTimeTesting), $DayStartTime));

var DateTime OnHoldStopDateTimeTesting = If(IsNull($OnHoldStopClock), Now() ,$OnHoldStopClock);

var DateTime OnHoldStopDateTime = Min($OnHoldStopDateTimeTesting, ToTimestamp(ToDate($OnHoldStopDateTimeTesting), $DayEndTime));

var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime),ToDate($StartDateTime)) - WeekdaySub(ToDate($OnHoldStopDateTime),ToDate($OnHoldStarDateTime)) + 1; //(we count each day as a full workday)

var number HoursBeforeStartEndAdjustment  = $WeekDayDays * ToHours($DayEndTime - $DayStartTime);

var datetime StartDateTimeBounded = 
  If(ToTimeOfDay($StartDateTime) < $DayStartTime, ToTimestamp(ToDate($StartDateTime), $DayStartTime), $StartDateTime);

var datetime EndDateTimeBounded = 
If(ToTimeOfDay($EndDateTime) > $DayEndTime, ToTimestamp(ToDate($EndDateTime), $DayEndTime), $EndDateTime);

var datetime OnHoldStartDateTimeBounded = 
  If(ToTimeOfDay($OnHoldStarDateTime) < $DayStartTime, ToTimestamp(ToDate($OnHoldStarDateTime), $DayStartTime), $OnHoldStarDateTime);

var datetime OnHoldStopDateTimeBounded = 
If(ToTimeOfDay($OnHoldStopDateTime) > $DayEndTime, ToTimestamp(ToDate($OnHoldStopDateTime), $DayEndTime), $OnHoldStopDateTime);

Max(0,
Round($HoursBeforeStartEndAdjustment 
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime)) 
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded)))
, Max(0,ToHours(ToTimeOfDay($OnHoldStartDateTimeBounded) - $DayStartTime)) 
- Max(0,ToHours($DayEndTime - ToTimeOfDay($OnHoldStopDateTimeBounded)))),0.1)

------------------------------
manohar vankireddy
------------------------------