Column to Calculate the number of workdays between two date columns

[HansTheEnforcer]

Hello All,

I'm trying to calculate the number of Non-Weekend days between two dates.  Basically what I've done is create a date table 'CountWorkingDays' that contains all dates from 1/1/2016 to Today and a column that numerically indicates the day of the week (Sun-Sat, 1-7).

The calculated column I'm using is called '2to3'

The Date columns I'm comparing are 'DateSentToVRMGroup' and 'DateApprovedProcessed'

The formula I'm trying to use is:

2to3 = if(or(ISBLANK([DateSentToVRMGroup]),ISBLANK([DateApprovedProcessed])),9999999,if([DateSentToVRMGroup]>[DateApprovedProcessed],0,CALCULATE(COUNT(CountWorkingDays[DayofWeek]),FILTER(CountWorkingDays,CountWorkingDays[Date]>[DateSentToVRMGroup] && CountWorkingDays[Date]<[DateApprovedProcessed] && CountWorkingDays[DayofWeek]<>1 && CountWorkingDays[DayofWeek]<>7))))

The formula isn't generating an error but all of the rows are coming up blank.  Is there something I'm doing wrong?

Thanks in advance for the help!

[Phil_Seamark · Accepted solution]

Hi @HansTheEnforcer

You could try adding the following calculated column.  Just replace 'Table2' with your table name.

No date table requried for this one 🙂

Count of Weekdays = 
VAR StartDate = DATE(2016,1,1)
VAR EndDate = TODAY()
VAR WeekDayTable = SELECTCOLUMNS(FILTER(ADDCOLUMNS(CALENDAR(StartDate,EndDate),"WeekDay",IF(WEEKDAY([Date])in {1,7},0,1)),[WeekDay]=1),"Week Day",[Date])
VAR Result = COUNTROWS(CALCULATETABLE(filter(CROSSJOIN(WeekDayTable,Table2),'Table2'[DateSentToVRMGroup]<[Week Day] && [DateApprovedProcessed]>[Week Day])))
RETURN Result

[HansTheEnforcer]

Thanks, it worked great!!!