Discussions

Expand all | Collapse all

Calculate Business Hours between today's date and Future Date

  • 1.  Calculate Business Hours between today's date and Future Date

    Posted 05-30-2018 21:00
    So I currently have a need to calculate the number of business hours (based on a 40 hour work-week) between the current date and a future date (date field "Completion Date"), not including weekends.

    Is this a simple formula to pull-off? 

    Thanks in advance for the assist.


  • 2.  RE: Calculate Business Hours between today's date and Future Date

    Posted 05-30-2018 21:07
    re: "simple formula"

    Someone posted this at one time on this forum.  If its your lucky day, it might work.  Let me know.
    It probably works.



    var timeofday DayStartTime = ToTimeOfDay("8:30 am");
    var timeofday DayEndTime = ToTimeOfDay("5:00 pm");

    var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
     
    var DateTime EndDateTimeTesting = If(IsNull([Status End]), Now() ,[Status End]);

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


  • 3.  RE: Calculate Business Hours between today's date and Future Date

    Posted 05-31-2018 14:21
    Yikes...yea, simple, indeed.

    Thank you for passing this one along - I've been tinkering with this, and know that I need to ID a start and end date, but that leaves me with a couple of questions that you might be able to answer for me:

    1. The start and stop can be determined with two calendar fields (appropriately named), correct? In this instance, to follow the pasted coded, 'Status Start' and 'Status End'?

    2. In line 4 of the code, there are two references to 'Status Start':

    var DateTime StartDateTime = Max([Status Start], ToTimestamp(ToDate([Status Start]), $DayStartTime));
     
    Not being a coding wizard, I am assuming the first reference is to my calendar field 'Status Start'?  If so, what is the second reference after 'ToTimestamp'? 

    Here is the error message that I get from that second reference:

    The types of the arguments or the number of arguments supplied do not meet the requirements of the function ToDate.

    The function ToDate can be used with the following arguments:
    • ToDate (Text x).
    • ToDate (Text x, Text f).
    • ToDate (Date/Time x).
    Any insight/clarity would be greatly appreciated!


  • 4.  RE: Calculate Business Hours between today's date and Future Date

    Posted 06-01-2018 00:15
    In that example code, the [Status Start] field is a date/time type field.  Since you have asked for business hours, then the formula needs to know the start date and the start time.

    Do you have fields for the start and end or do you currently have these as separate fields for the start date and the start time and the end date and the end time.


  • 5.  RE: Calculate Business Hours between today's date and Future Date

    Posted 06-05-2018 15:08
    Late response (extended weekend).  Appreciate the guidance.

    Yup, my calendar entry was simply 'Date'...and as soon as I read your reply reminding me of the hour aspect, I felt stupid.

    I changed the field to 'Date / Time' and it works great - thanks again for the help!


  • 6.  RE: Calculate Business Hours between today's date and Future Date

    Posted 06-05-2018 15:58
    Great, thx for letting me that the formula works!


  • 7.  RE: Calculate Business Hours between today's date and Future Date

    Posted 06-05-2018 17:20

    You bet - thanks for passing on it on. 

    One item that I have noticed is that you do have to be specific about logging the time (in addition to date).

    For example, I have it set to calculate on a 30-hour work-week, so selecting the Monday on my project start time and Friday on my project end-time SHOULD calculate an even 30 hours. (in the formula I have it set to 08:00 AM - 02:00 PM).

    What I'm finding is, if I don't type in the actual time-stamp in the calendar fields (ie 06-04-2018 08:00 AM - 06-08-2018 02:00 PM) it will skew the numbers (even though the times are set in the formula).

    Other than that little wrinkle, it works beautifully for me needs.