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.
Solved! Go to Solution.
Hi Ceri,
This requirement can be acheived in the following manner:
1) Create a module(say, "Time Mappings") to maintain the working day mappings with time scale set as "Day", as shown in below screenshot:
a) Working Day Check (Line item 1): A boolean formatted line item
b) Cumulative Working Days (Line item 2): A number formatted line item with formula "CUMULATE(IF Working Day Check THEN 1 ELSE 0)"
The first line should be maintained manually and it will be a yearly activity done by an admin (Working days as to be checked manually, as shown in screenshot below)
2) An example module to hold your start and end dates and to calculate number of working days:
Formula for calculating number of working days = "Time Mappings.Cumulative Working Days[LOOKUP: Date Input.End Date] - Time Mappings.Cumulative Working Days[LOOKUP: Date Input.Start Date] + 1"
Based on the inputs given in Step 1 and Step 2, you can see the number of working days as "5" in Jan 17 column.
Hope this example will solve your query. Let me know, if you need further explanation on this.
Regards,
CA Pavan Kumar
That's great, thanks for your help.
There is a formula on this page
that will help translate from the excel NETWORKDAYS function to Anaplan. Hope this helpg
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:
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.