Date/Time duration formula in hours, excluding 12am-4am

Hi Sherry,

Would it ever be the case that an outage would be resolved in that 12-4 AM gap you don't want to count or would it be that they wouldn't be resolved in that time frame? Would they go for longer then 1 day potentially? Say 3 days so those hours would need to be excluded for 12-4 AM multiple times?

It is a difficult formula.  Like a Pack Rat, I once saved this beaut.  Let me know if it works.

// input your own business hours and datetime fields which are your start and end. 

var timeofday DayStartTime = ToTimeOfDay("4:00 am");
var timeofday DayEndTime = ToTimeOfDay("12:01 am");
var datetime StartClock = [Date Created];
var datetime EndClock = [Closed 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 Number WeekDayDays = WeekdaySub(ToDate($EndDateTime), ToDate($StartDateTime)) + 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);

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

The field type should be formula numeric. If that does not work please post the complete formula.

BINGO!!  It works perfectly.  Thank you so much! We are happy dancing over here.

Spoke too soon.  When the outage range includes Saturday or Sunday, it is returning 0 hours for those days.

Right, the formula was designed to count business hours not counting weekends. But I am pretty sure I know how to change the formula to adjust for that. But I�m in a car now, so I will get to it when I can.

Here is a revised formula.  There is probably a simpler formula given that you are not excluding weekends, but since this seems to work, it should do the trick.  I did have to use 11:59 instead of 12:00 to get it to work, so I also changed the 4:00 back one minute so that the results did not have unexpected decimals.

// input your own business hours and datetime fields which are your start and end. 

var timeofday DayStartTime = ToTimeOfDay("3:59 am");
var timeofday DayEndTime = ToTimeOfDay("11:59 pm");
var datetime StartClock = [Start Date Time];
var datetime EndClock = [End Date Time];

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 Number DayDays = ToDays(ToDate($EndDateTime) - ToDate($StartDateTime) + Days(1)); //(we count each day as a full workday) 

var number HoursBeforeStartEndAdjustment  = $DayDays * 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);

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