Working Days between dates

CommunityMember83188

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.

Tags

Modeling

Accepted answers

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

All comments

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

CommunityMember83188

That's great, thanks for your help.

Cheers!

Pavan

There is a formula on this page

https://help.anaplan.com/anapedia/Content/Calculation_Functions/Function%20Usage/Excel%20Equivalent%20Formulas.htm#NETWORKDAYS

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:

Create Date Input Module
1. Create 2 date formatted lines for start and end date
2. 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]

Date Input - Working Day.PNG

Create Time Mappings module with a timescale set to Day
1. Create a number formatted line item to pull in the day of the week for that specific date. Formula: WEEKDAY(START())
2. 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
3. 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
4. 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)

Time Mapping - Working Day.PNG

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.

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Postman/Anaplan - message "404, Not found"
Hi all, I, as an Anaplan workspace administrator, am working together with a Fabric team to export data from Anaplan to Fabric via Anaplan API and Postman. We have a problem where all Anaplan exports are visible in Postman but when trying to connect to SOME of them, we get message "404, Not found". Below screenshot showing…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…