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

• 1
• Question
• Updated 8 months ago
I am calculating elevator/escalator outages and need a formula that will calculate the hours between two Date/Time fields "Incident Start Date/Time" and "Incident End Date/Time".  The kicker is I need the formula specifically to exclude the hours of 12am-4am. Thanks in advance for your help!
• 152 Points Posted 9 months ago

• 1

Evan Martinez, Community Manager

• 12,028 Points 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?

• 152 Points The reason for the exclusion is that the equipment is not in service during those hours.  So we want those hours removed from the calculation, so that the outage hours only reflect the hours when the equipment should be operational for service, but is not.

It is highly unlikely, but not impossible, that an outage would be resolved during those hours.  An outage could last more than one day, so those hours would need to be excluded for every 24 hours.
• 152 Points I'm getting this error. The field type is "Formula-Duration".
• 70,384 Points It is a difficult formula.  Like a Pack Rat, I once saved this beaut.  Let me know if it works.

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,
- Max(0,ToHours(ToTimeOfDay(\$StartDateTimeBounded) - \$DayStartTime))
- Max(0,ToHours(\$DayEndTime - ToTimeOfDay(\$EndDateTimeBounded))),0.1))

• 70,384 Points The field type should be formula numeric. If that does not work please post the complete formula.
• 152 Points I changed the field type, but it is returning 0 no matter what date/time I put in the start and end fields.  I don't think I need the var Number WeekDayDays.  Our stations operate 365 days a year, round the clock with the exception of midnight to 4am.

This is by far the most complicated formula I've worked on, so feel free to treat me as a beginner :)

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

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,
- Max(0,ToHours(ToTimeOfDay(\$StartDateTimeBounded) - \$DayStartTime))
- Max(0,ToHours(\$DayEndTime - ToTimeOfDay(\$EndDateTimeBounded))),0.1))

• 70,384 Points I will give it a try in the next few days.
• 70,384 Points I did a test and the formula works if you fix my error here

var timeofday DayStartTime = ToTimeOfDay("4:00 am");
var timeofday DayEndTime = ToTimeOfDay("11:59 pm");

Let know how it tests.
• 152 Points BINGO!!  It works perfectly.  Thank you so much! We are happy dancing over here.
• 152 Points Spoke too soon.  When the outage range includes Saturday or Sunday, it is returning 0 hours for those days.
• 70,384 Points 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.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,204 Points I have a formula that is easy to understand that I will post shortly.

Three questions:

(1) Is there a increment of time you want the formula to use? Like 1 minute, 15 minutes or an hour?

(2) Do you want to round up or round down?

(3) What type of output do you want? Numeric or Duration?
(Edited)
• 70,384 Points 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.

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,
• 152 Points • 70,384 Points 