Add number of business days to a date

eway
eway
in Modeling

Hi,

 

I'm trying to find a date that is day 5 or 7 or 16 business days later than Start Date, based on a cell that contains Num Biz Days.  I've created a Calendar module where I exclude weekends and holidays I've marked such that I have a Cumulative Working Days where I can Lookup Start date (say it returns 341th working day), add my Num Biz Days, but how could I then return the ending date dimension back? Or another idea that works better with lists.

 

Thanks!

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @eway 

    I have a solution but there are a number of steps.

    The principles I followed here;

    1. Take the start date and add a number of days to create an end date
    2. Apply a series of queries against that end date to create an end date adjustment
      1. Weekend
      2. Holiday
    3. Add the adjustment to the end date to arrive at a final end date

    The following screenshots should take you through the various modules and line items. Where the formula is too large I have included individual screenshots so you can work through them in full.

     

    First here is the summary module where the user will enter a start date and a number of business days reference

    ChrisHeathcote_0-1620720325883.png

     

    ChrisHeathcote_1-1620720401024.png

     

    Secondly, here is the calendar module where the adjustment is calculated

     

    ChrisHeathcote_2-1620720451500.png

     

    ChrisHeathcote_3-1620720469717.png

     

    ChrisHeathcote_4-1620720487591.png

     

    It is not necessary to create a fake time frame using a numbered list as I think has already been suggested. 

    Using fake time should always be the option of last resort as you have already pointed out all the time functions are then redundant.

     

    Good luck

     

Answers

  • jjayavalli
    jjayavalli

    Hi,

     

    See if this helps. 

     

    https://community.anaplan.com/t5/On-Demand-Courses/Optimizer-Dynamic-Time-Setup/ta-p/74604?attachment-id=2651

     

    Thank you

    Jaya

  • eway
    eway

    Hi Jaya,

     

    Thanks so much, this is along the lines of what I was trying with the numbered list. If I dimension my module by Dynamic time (the new Days list), then I can't use Previous/Next on the numbered list dimenion in the same way.  I also can't use a FINDITEM to pull the new date based on cumulative business days or similar, or step through the list of days, at least the numerous ways I've tried.  How would I go about moving from a Start Date, 5 business days later through my Dynamic time, and getting back an End Date?

     

    Thanks!

  • eway
    eway

    So creative, the piece I was missing, thank you!

     

    I added this Calendar to a list dimension to calculate a few at a time for different dates (and use a few year Time Range on this model to limit the daily module size) and lookup my current reporting period to pull a set of business dates for this period. I can then save the resulting business dates each month/quarter to a module without the daily calendar calcualtor.

     

    One small edit in case anyone else tries to replicate - Holiday Day Ref needs to read "If Date Range AND Holiday THEN 1 ELSE 0" otherwise you end up adding all of your holidays over the periods to every date's adjustment.

     

    Thank you again!

Categories