Working Days between dates

I have a use case where I have to check that the assigned task days to a project fit within the days available between the project start and end dates, after accounting for weekends. I initially had a solution that excluded Saturdays and Sundays, but I wanted something more flexible so I have a solution that allows for different working days per project. This obviously could be adapted to account for working pattertns for employees too if required. I have attached the steps in the document. Hope it helps David

Accepted answers

PrevContributor

Hi David
I've developed another example (could be more simple)

1. Create a Module "Calendar" using Day Timescale and 3 lineitems
- Days of week (to calc number of day in week)
- Holidays (to mark holidays)
- Working Days Cumulation (to calc number of working days except weekends and holidays)
Please see attache picture for more info (Note: it's for Model with TimeScale that starts from 2014/01/01)

2. In target module where you have Start date and End Date
use lineitem Working days with formula = Calendar.Working Days Cumulation[lookup: End Date]-Calendar.Working Days Cumulation[lookup: Start Date]

Hope it helps 😄

All comments

PrevContributor

simon_ritchie

Hi Oleg,

I used your suggestion in a model recently to calc weekdays for each date in the timescale.
In the absense of a weekday() function, I like you're idea of telling Anaplan the Weekday of the first date in the Model, and then a formula to work out the weekday for every other date.
I have to share this model with other people, and wanted to avoid a situation when someone changes the time settings and forgets (or doesn't realise) they need to update this formula...

I finally came up with a slight variant to your approach, it calculates the number of days between the current date and a predefined date (I chose 1/1/2012 its a Sunday), if I know the number of days between the two dates, and the weekday of one of the dates, I can workout the weekday of the second date.

I have a list called Weekday, I have added weekday numbers in the "Code"... *see screenshot*

This is the formula:
FINDITEM(Weekdays, TEXT(MOD(7 + MOD(Start() - DATE(2012, 1, 1) + 1, 7) - 2, 7) + 1))

With the above formula, there is no need to edit the formula if you change the Model time settings.

Would never have thought of it if not for your original idea! Many thanks!
Simon

PrevContributor

H Simon
Thank you for sharing
I see you've created very good additions to weekday formula: using MOD, Code and predefined date
Believe we could win a challenge between us and anaplan developers on new functions construction :unsure:

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…