Forum Discussion

JamieBrown's avatar
JamieBrown
Qrew Trainee
6 years ago

Duration Formula minus weekend hours (smart Unit result)

I am trying to create a duration formula field that takes the Completed Date minus Opened Date to tell me how long the job was in process.  This often includes weekend time which should not be included.  My original result (including weekend time) gave me results such as 1.91 days, 18.71 hours, 6.90 days, etc...aka, smart unit duration results.

Is there a way to subtract 48hours of duration time if the duration spans a weekend?

jobs would not be open ON a weekend, so the weekend time would only ever ben 2 days/48hrs.  But i really want to keep the smart units duration format on the results if possible.

(i've been able to use WeekdayAdd & toDays to be able to get whole days results (ie, 2 days, 1 day, 5 days), but I really prefer the more specific results without rounding to whole days).

------------------------------
Jamie Brown
------------------------------
Formulas and functions
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    6 years ago
    Try this formula below - this is assuming that [Completed Date] and [Open Date] are Date/Time fields.

    It first looks for [Completed Date] and [Open] date within the same 5 day period in the same week - in which case there are no weekend hours to remove.

    Then it checks for the duration being within 5 days, but crossing a weekend - in which case you remove 48 hours.

    Finally - it checks for the duration spanning multiple weekends, and removes the total weekend hours.

    var Duration TotalDuration = [Completed Date] - [Open Date];

    If (
    // if less than 7 days and within the same week, then there are no weekend hours to remove
    $TotalDuration <= Hours(168) 
    and DayOfWeek(ToDate([Completed Date])) > DayOfWeek(ToDate([Open Date])),
    $TotalDuration,

    // if less than 7 days, but crosses a weekend - then remove 48 hours
    $TotalDuration <= Hours(168),
    $TotalDuration - Hours(48),

    // otherwise remove the hours belonging to multiple weekends
    $TotalDuration - Hours(Int(ToHours($TotalDuration) / 168) * 48)
    )


    ------------------------------
    Xavier Fan
    Quick Base Solution Provider
    http://xavierfanconsulting.com/
    ------------------------------