Add number of business days to a date
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.
I have a solution but there are a number of steps.
The principles I followed here;
- Take the start date and add a number of days to create an end date
- Apply a series of queries against that end date to create an end date adjustment
- 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
Secondly, here is the calendar module where the adjustment is calculated
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.
See if this helps.
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?
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!1