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

  • 1
  • 1
  • Question
  • Updated 1 month ago
  • Answered
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!
Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb

Posted 2 months ago

  • 1
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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?

Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb
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.
Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb
I'm getting this error.

The field type is "Formula-Duration".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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))


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
The field type should be formula numeric. If that does not work please post the complete formula.
Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb

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

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
I will give it a try in the next few days.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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");


I had those backwards.

Let know how it tests.
Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb

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

Sherry Blackburn

  • 152 Points 100 badge 2x thumb

Spoke too soon.  When the outage range includes Saturday or Sunday, it is returning 0 hours for those days.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,572 Points 20k badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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))
Photo of Sherry Blackburn

Sherry Blackburn

  • 152 Points 100 badge 2x thumb
That's it!!  Works beautifully.  Thank you so much for your help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Ok. Good luck with keeping the elevators & escalators running