Add number of business days to a date

eway
Occasional Contributor

Add number of business days to a date

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Add number of business days to a date

@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

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

4 REPLIES 4
jjayavalli
Contributor

Re: Add number of business days to a date

eway
Occasional Contributor

Re: Add number of business days to a date

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!

ChrisHeathcote
Community Boss

Re: Add number of business days to a date

@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

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

eway
Occasional Contributor

Re: Add number of business days to a 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!