Working Days between dates

[Ceri]

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.

[PavanKumar · Accepted 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

[Ceri]

That's great, thanks for your help.

[PavanKumar]

Cheers!

Pavan

[egc8r]

There is a formula on this page 

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

that will help translate from the excel NETWORKDAYS function to Anaplan.  Hope this helpg

[Tiffany.Rice]

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: 

1. 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]

                    

1. 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)

                   

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.