Add number of business days to a date

eway

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!

ChrisAHeathcote

@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

 

 

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.

 

Good luck