Discussions

Expand all | Collapse all

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

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

    Posted 10-03-2018 15:56
    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!


  • 2.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-04-2018 15:34
    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?



  • 3.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-05-2018 15:55
    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.


  • 4.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-10-2018 22:22
    I'm getting this error.">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1756100/RackMultipart20181010-80305-1pzz81n-Capture_inline.PNG?1539210109">

    The field type is "Formula-Duration".


  • 5.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-05-2018 20:47
    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))




  • 6.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-10-2018 22:55
    The field type should be formula numeric. If that does not work please post the complete formula.


  • 7.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-10-2018 23:15

    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))



  • 8.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-10-2018 23:24
    I will give it a try in the next few days.


  • 9.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-12-2018 01:58
    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.


  • 10.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-12-2018 15:43

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


  • 11.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-12-2018 15:56

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


  • 12.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-12-2018 16:56
    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.


  • 13.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-12-2018 17:13
    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?


  • 14.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-13-2018 02:02
    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))


  • 15.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-15-2018 16:15
    That's it!!  Works beautifully.  Thank you so much for your help.


  • 16.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 10-15-2018 16:36
    Ok. Good luck with keeping the elevators & escalators running


  • 17.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 7 days ago
    how will this formula works if I want to exclude weekends? I only want to include regular business hours 9-5 Monday to Friday!

    ------------------------------
    Endy Lu
    ------------------------------



  • 18.  RE: Date/Time duration formula in hours, excluding 12am-4am

    Posted 7 days ago
    Go with one of the original formulas above. If you read through the thread there is a point where they got the formula to work perfectly for their needs except it was still excluding weekends. I'd imagine if you took that formula and changed the start and end times to be 9 am and 5 pm that it would work for you or be really close to working for you and only need some small adjustments.