Hi all,
I've search all round this forum to find a simple solution to figure out number of working days between two dates. i.e. excluding weekend. I actually had to use two existing posts and some existing skills to make it a bit easier to understand.
All dates in my example are hypothetical....Here it goes:
Start with creating new table that will act as a calendar
Next use this in formula bar of newly created table (you're giving it a name and it's first column)
MyCalendar =
CALENDAR(MIN('Activivty History'[Latest Baseline Dispatch Date].[Date]),MAX('Sub Project w Activities'[ACTUAL_START].[Date]))
Explanation: CALENDAR funtion returns a table that contains a set of dates, it names your column simply as Date, whereas the usage of MIN and MAX indicates your date ranges of your date set. So I'm using MIN date found in Latest Baseline Dispatch Date column and MAX date found in Actual Start column. You should see something like this so far:
Next we need to figure out week days within date range we just created. Create new column and copy and paste this:
Week Day = WEEKDAY(MyCalendar[Date].[Date],2)
As per documentation this function "Returns a number from 1 to 7 identifying the day of the week of a date". Syntax for this is:
WEEKDAY(<date>, <return_type>). There are three different return types, read here more about it. I have used 2 in above code as it indicated week beggining on Monday (1) and ending on Sunday (7). You should now have something like this:
You can see your 6 & 7 which is your Saturday and Sunday (feel free to check the calendar like I did to see if this was right)
Next what you want to do is create a simple IF statement that determines if it's a working day or not, so use this:
Working Day = IF(DimDate[Week Day] in {1,2,3,4,5},"True","False")
This IF statement simply goes and checks what the week day is and if it falls within 1-5 range returns True otherwise it returns False.
Now return to your table where you want to figure out your working days between two dates and do this:
WorkingDays Early or Late =
COUNTROWS(
FILTER(MyCalendar,
AND(
AND(
MyCalendar[Date].[Date] >='Activivty History'[Latest Baseline Dispatch Date].[Date],
MyCalendar[Date].[Date] <=RELATED('Sub Project w Activities'[ACTUAL_START].[Date])),
MyCalendar[Working Day])))
You're counting number of rows within filtered MyCalendar table you have created where your date range is (in my example) between Latest Baseline Dispatch Date and Actual Start and it only counts the ones that are True in Working Day calendar.
Hope this helped someone out there!
Thanks
Kasia
