Forum Discussion

KevinTrostle's avatar
KevinTrostle
Qrew Member
5 years ago

Hour Calculation

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)),
ToTimeOfDay(ToText($SStart)));

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

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

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



------------------------------
Kevin T
------------------------------

17 Replies

  • 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,
    Round($HoursBeforeStartEndAdjustment
    - 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
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • DirkRuana's avatar
      DirkRuana
      Qrew Captain
      Hi Mark:

      var datetime StartClock = [Date Created];
      var datetime EndClock = [Date Created]+[Response Duration];

      I am trying to use this formula with one modification: The calculation for var datetime Endclock.

      I am receiving the following error notice: Expecting datetime but found number (I am sure this is the duration number in my calculation).

      Can I modify my syntax so this will work?

      Thank you,

      Dirk


      ------------------------------
      Dirk Ruana
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        What field type is [Response Duration]

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------