Forum Discussion

SteffanieJ's avatar
SteffanieJ
Frequent Visitor
2 years ago
Solved

Calculate NON-Work Days between two dates

Hello!  I am trying to calculate the number of NON-Work Days between two dates but all I can find is how to get working days.  

 

This is my Excel formula.

I do have a calendar set up with these columns but I don't know how to filter for the 0's in "If Work Day" column.

Any help would be greatly appreciated.

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    SteffanieJ , Add a calculated column to add weekends 

     

    IsWeekend =
    IF (
    WEEKDAY(DateTable[Date], 2) >= 6,
    TRUE,
    FALSE
    )

     

    And one more to identify holiday 

    IsHoliday =
    IF (
    CALCULATE (
    COUNTROWS ( Holidays ),
    Holidays[HolidayDate] = DateTable[Date]
    ) > 0,
    TRUE,
    FALSE
    )

     

    And after that Combine weekends and holidays to create a column identifying all non-work day

    IsNonWorkDay =
    DateTable[IsWeekend] || DateTable[IsHoliday]

     

    Finally, create a measure or a calculated column to calculate the number of non-work days between two dates. Assuming you have a start date and end date in your data model, you can create a measure like this:

    NonWorkDaysBetween =
    CALCULATE (
    COUNTROWS ( DateTable ),
    FILTER (
    DateTable,
    DateTable[Date] >= MIN ( YourTable[StartDate] ) &&
    DateTable[Date] <= MAX ( YourTable[EndDate] ) &&
    DateTable[IsNonWorkDay]
    )
    )

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    SteffanieJ , Add a calculated column to add weekends 

     

    IsWeekend =
    IF (
    WEEKDAY(DateTable[Date], 2) >= 6,
    TRUE,
    FALSE
    )

     

    And one more to identify holiday 

    IsHoliday =
    IF (
    CALCULATE (
    COUNTROWS ( Holidays ),
    Holidays[HolidayDate] = DateTable[Date]
    ) > 0,
    TRUE,
    FALSE
    )

     

    And after that Combine weekends and holidays to create a column identifying all non-work day

    IsNonWorkDay =
    DateTable[IsWeekend] || DateTable[IsHoliday]

     

    Finally, create a measure or a calculated column to calculate the number of non-work days between two dates. Assuming you have a start date and end date in your data model, you can create a measure like this:

    NonWorkDaysBetween =
    CALCULATE (
    COUNTROWS ( DateTable ),
    FILTER (
    DateTable,
    DateTable[Date] >= MIN ( YourTable[StartDate] ) &&
    DateTable[Date] <= MAX ( YourTable[EndDate] ) &&
    DateTable[IsNonWorkDay]
    )
    )