Expand all | Collapse all

Hour Calculation

  • 1.  Hour Calculation

    Posted 11-20-2019 15:00

    Hello, I'm trying to calculate the amount of hours between two datetime fields only including times of operation and not including weekdays. I've thrown my brain against this for entirely too long and below is the spaghetti I've come up with. I know the request and response variable aren't working as I need. If anyone can offer advice or help cleaning up/making the code work, I'd appreciate it! Thank you!

    var datetime StartD=[Date and Time of Request]; // Declaring Start Date - Days
    var datetime EndD=[Date and Time of Response]; // Declaring End Date - Days

    var timeofday SStart = ToTimeOfDay([Date and Time of Request]);
    var timeofday EStart = ToTimeOfDay([Date and Time of Response]);
    var timeofday SEnd = ToTimeOfDay([Date and Time of Request]);
    var timeofday EEnd = ToTimeOfDay([Date and Time of Response]);

    var datetime StartH=[Date and Time of Request]; // Declaring Start Date - Hours
    var datetime EndH=[Date and Time of Response]; // Declaring End Date - Hours

    var date SDate=ToDate($StartD); // Declaring SDate and Converting to Date
    var date EDate=ToDate($EndD); // Declaring EDate and Converting to Date

    var number NumberOfDays=WeekdaySub($EDate, $SDate); // Finding Intergers of Days between Start and End

    var TimeofDay STime=ToTimeOfDay($StartD); //Declaring Start Time variable STime
    var TimeofDay ETime=ToTimeOfDay($EndD); //Declaring End Time variable ETime

    var timeofday Request = If((ToNumber(ToText($SStart)) < 8) , ToTimeOfDay(ToText(8)), (ToNumber(ToText($SStart)) > 8), ToTimeOfDay(ToText(16)),

    var timeofday Response = If((ToNumber(ToText($SEnd)) < 8) , ToTimeOfDay(ToText(8)), (ToNumber(ToText($SEnd)) > 8), ToTimeOfDay(ToText(16)),

    var duration ttime = Abs($Request-$Response);

    Hours(($NumberOfDays*12)+ToNumber(ToText($ttime))) // Returning a Duration from a number of Days

    Kevin T

  • 2.  RE: Hour Calculation

    Posted 11-20-2019 15:11
    I can't help debug you formula, but apparently this one works. It excludes weekend days and you set your own business hours.  Let us know if it works for you.

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

    var timeofday DayStartTime = ToTimeOfDay("6:00 am");
    var timeofday DayEndTime = ToTimeOfDay("6:00 pm");
    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,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
    - Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded))),0.1))

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach