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

 

and the result Id like to have in format : 10.75 days 

 

Could you advise/help or redirect to correct post about this topic ? Thanks. Maciej 

 

 

  • 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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This would not help . Networkdays are working days. 

      • daXtreme's avatar
        daXtreme
        Solution Sage
        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng