Forum Discussion

manoharvankired's avatar
manoharvankired
Qrew Trainee
2 years ago

How to calculate the number of hours between date/time fields, excluding weekends

I have two fields date/time I want to calculate extract hours the between those dates. Can someone guide me here 



------------------------------
manohar vankireddy
------------------------------
  • Hey Manohar,

    Easiest way to get this done would be to create a Formula - Duration field and input the formula below.

    [Date Two]-[Date One]

    Make sure under the "display" section you change the units dropdown to "Hours". (Will be defaulted to "Smart Units")

    Hope this helps!



    ------------------------------
    George Hambel
    Project Manager
    Synctivate Inc
    ------------------------------

    • manoharvankired's avatar
      manoharvankired
      Qrew Trainee

      Hi George,

      Here my formula , Field type is numeric but I want to calculate exact hours

      Note: business hours excluding it is fine

      var timeofday DayStartTime = ToTimeOfDay("12:00");
      var timeofday DayEndTime = ToTimeOfDay("21:00");
      var datetime StartClock = [Start Date];
      var datetime EndClock = [Completed Date];
      var datetime OnHoldStartClock = [On Hold Start Date];
      var datetime OnHoldStopClock = [On Hold Stop 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 DateTime OnHoldStarDateTimeTesting = If(IsNull($OnHoldStartClock), Now() ,$OnHoldStartClock);

      var DateTime OnHoldStarDateTime = Max($OnHoldStarDateTimeTesting, ToTimestamp(ToDate($OnHoldStarDateTimeTesting), $DayStartTime));

      var DateTime OnHoldStopDateTimeTesting = If(IsNull($OnHoldStopClock), Now() ,$OnHoldStopClock);

      var DateTime OnHoldStopDateTime = Min($OnHoldStopDateTimeTesting, ToTimestamp(ToDate($OnHoldStopDateTimeTesting), $DayEndTime));

      var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime),ToDate($StartDateTime)) - WeekdaySub(ToDate($OnHoldStopDateTime),ToDate($OnHoldStarDateTime)) + 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);

      var datetime OnHoldStartDateTimeBounded = 
        If(ToTimeOfDay($OnHoldStarDateTime) < $DayStartTime, ToTimestamp(ToDate($OnHoldStarDateTime), $DayStartTime), $OnHoldStarDateTime);

      var datetime OnHoldStopDateTimeBounded = 
      If(ToTimeOfDay($OnHoldStopDateTime) > $DayEndTime, ToTimestamp(ToDate($OnHoldStopDateTime), $DayEndTime), $OnHoldStopDateTime);

      Max(0,
      Round($HoursBeforeStartEndAdjustment 
      - Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime)) 
      - Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded)))
      , Max(0,ToHours(ToTimeOfDay($OnHoldStartDateTimeBounded) - $DayStartTime)) 
      - Max(0,ToHours($DayEndTime - ToTimeOfDay($OnHoldStopDateTimeBounded)))),0.1)



      ------------------------------
      manohar vankireddy
      ------------------------------

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        If you just want this to be a formula Duration field type, then make that change and convert it to a Duration like this :

        Hours(

        Max(0,
        Round($HoursBeforeStartEndAdjustment 
        - Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime)) 
        - Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded)))
        , Max(0,ToHours(ToTimeOfDay($OnHoldStartDateTimeBounded) - $DayStartTime)) 
        - Max(0,ToHours($DayEndTime - ToTimeOfDay($OnHoldStopDateTimeBounded)))),0.1)

        )



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------