I'm looking to try and find away to calulate working days between two dates in a module; I know this has been addressed in a previous post, but it's from 2014 and I can't seem to access the supporting docs. that really show me what I need.
I'm a new model builder so any assitance would be appriciated, and apologies if this has been covered elsewhere and I've not found it.
This is something my team has been trying to solve for as we needed to calculate the # of working days in each month for personnel planning. The provided solution is workable but had a bit more manual admin work than would be ideal for our situation. I was able take the outline provided and leverage the WEEKDAY() formula to automate the determination of which dates to include/exclude from working days.
See below for breakdown of my solution:
Create Date Input Module
Create 2 date formatted lines for start and end date
Create number formatted line item for # of cumulative Working Days as of the end date (this will reference the module created in the next step) Formula: Time Mappings.Cumulated Working Days[LOOKUP: End Date]
Create Time Mappings module with a timescale set to Day
Create a number formatted line item to pull in the day of the week for that specific date. Formula: WEEKDAY(START())
Create a number formatted line item to determine the number of Working Days. The formula will define which days of the week will be your non-working day (in the US that is generally Saturday (day 6) and Sunday (day 7). Formula: IF Day of Week = 6 OR Day of Week = 7 THEN 0 ELSE 1
Create Boolean formatted line item to determine which dates have working days between the two specified dates. Formula: START() >= Date Input.Start Date AND END() <= Date Input.End Date AND Working Day > 0
Create number formatted line it to calculate the cumulative number of working days where the Boolean is marked true. Formula: CUMULATE(IF Include in Cumulated? THEN 1 ELSE 0)
This solution allowed us to systematically calculate workday for all of our planning periods so wanted to share should it be helpful to anyone else in the future.