Working Days between dates

DavidSmith
DavidSmith
in Modeling

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

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    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 😄

Comments

  • simon_ritchie
    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
    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:

Categories