Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Networkdays

Hi All,   I'd like to know if there is a way to calculate networkdays between two different days with time i.e. [Creation Date] is 11/23/2021 3:30 pm [Completion Date] is 12/06/2021 9:04 am   a...
  • daXtreme's avatar
    daXtreme
    4 years ago
    // Something like this.
    // Dates is a calendar that stores
    // full days (with no time component).
    
    |---------|=====A=====|==========|---------|==========|---------|==========|----B----|
    
    // Legend: |====| -> non-working day, |---| -> working day
    
    var DateTime1 = A
    var Date1 = DATEVALUE( DateTime1 )
    var DateTime2 = B
    var Date2 = DATEVALUE( DateTime2 )
    var FirstWorkingAfterOrOnA =
        CALCULATE(
            MIN( Dates[Date] ),
            Dates[Date] >= Date1,
            Dates[Day Type] = "working"
        )
    var LastWorkingBeforeOrOnB =
        CALCULATE(
            MAX( Dates[Date] ),
            Dates[Date] <= Date2,
            Dates[Day Type] = "working"        
        )
    var StartDate =
        MAX( DateTime1, FirstWorkingAfterOrOnA )
    var EndDate =
        MIN( DateTime2, LastWorkingBeforeOrOnB )
    var NonworkingDays =
        CALCULATE(
            COUNTROWS( Dates ),
            Dates[Date] >= StartDate,
            Dates[Date] <= EndDate,
            Dates[Day Type] = "non-working"
        )
    var NetworkingDays =
        1 * (EndDate - StartDate) - NonworkingDays
    var Result =
        IF( NetworkingDays > 0,
            NetWorkingdays
        )
    return
        Result