Discussions

 View Only
  • 1.  How to calculate the number of hours between date/time fields, excluding weekends

    Posted 03-15-2023 07:59

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



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


  • 2.  RE: How to calculate the number of hours between date/time fields, excluding weekends

    Posted 03-23-2023 07:09
    Edited by George Hambel 03-23-2023 09:08

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



  • 3.  RE: How to calculate the number of hours between date/time fields, excluding weekends

    Posted 03-24-2023 11:11
    Edited by manohar vankireddy 03-24-2023 11:14

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



  • 4.  RE: How to calculate the number of hours between date/time fields, excluding weekends

    Posted 03-24-2023 11:24

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