Forum Discussion

misen13's avatar
misen13
Frequent Visitor
7 years ago
Solved

Networkingdays - Formula

Hi,

 

I'm trying to find the number of workingdays between two dates using:

 

 

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
    Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

 

However, I'm getting the value 125 for the period 1/1/2018 to 30/06/2018.

I've tested on Excel using the formula NETWORKINGDAYS but it returns 130 instead.

 

Why am I not getting the same number?

 

Thank you.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi misen13,

     

    Please new a calendar table first, similar to below:

    Dim date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 7, 31 ) ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

    Then, to count the working days, please refer to below DAX formula:

    Count Workingdays =
COUNTROWS (
    FILTER (
        'Dim date',
        'Dim date'[Date] >= EARLIER ( Table11[StartDate] )
            && 'Dim date'[Date] <= EARLIER ( Table11[EndDate] )
            && 'Dim date'[Weekday] >= 1
            && 'Dim date'[Weekday] <= 5
    )
)

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi misen13,

     

    Please new a calendar table first, similar to below:

    Dim date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 7, 31 ) ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

    Then, to count the working days, please refer to below DAX formula:

    Count Workingdays =
COUNTROWS (
    FILTER (
        'Dim date',
        'Dim date'[Date] >= EARLIER ( Table11[StartDate] )
            && 'Dim date'[Date] <= EARLIER ( Table11[EndDate] )
            && 'Dim date'[Weekday] >= 1
            && 'Dim date'[Weekday] <= 5
    )
)

     

    Best regards,

    Yuliana Gu