6 Replies Latest reply on Dec 14, 2009 5:19 PM by Yizwitz

# Date calc so end date doesn't fall on weekend or holiday

### Title

Date calc so end date doesn't fall on weekend or holiday

### Post

I'm a newer FM Pro 10 user.  I've looked through the forum for an answer to this issue but I don't quite find the answer.

I need to do a straightforward date calculation to determine due dates for certain items based on an initiation date for the process I'm tracking.  The deadlines simply need to be adjusted so that if the deadline falls on a Saturday or Sunday, it gets kicked ahead to the following Monday.  Similarly, if the deadline falls on a holiday (I have a list of the holidays through the calendar year), the deadline needs to be kicked to the next day.  So, for example, if the deadline falls on Christmas Day of 2009, which is a Friday, the deadline should then be set for Monday, December 28 (because Christmas falls on a Friday this year).

I don't need to worry about skipping weekends in the calculation.  All I need to worry about is whether the deadline falls on a weekend or holiday.

Any assistance would be appreciated.  Thanks a lot.

• ###### 1. Re: Date calc so end date doesn't fall on weekend or holiday
The weekends issue is quite simple: check the day-of-week of the projected deadline - if it's Sunday or Saturday, add 1 or 2. For example:

Let ( [
endDate = StartDate + 28 ;
dW = DayOfWeek (
endDate )
] ;
endDate + Case ( dW = 1 ; 1 ; dW = 7 ; 2 )
)

Dealing with holidays is a bit more tricky, because the following day could be a weekend day, and the day after that could be another holiday, etc. - so this would be a recursive calculation, using either a custom function (requires FMP Advanced) or a looping script.

• ###### 2. Re: Date calc so end date doesn't fall on weekend or holiday
DOH!  I knew I was overlooking something when I posted the thread.  I do have a copy of FMP 10 Advanced.  The function for dealing with weekends works like a charm, though!  Thanks a lot.  If you could supply some insight on a custom to address the holiday issue, that would be great.  If not, I appreciate the help.
• ###### 4. Re: Date calc so end date doesn't fall on weekend or holiday

I definitely get the logic of the CF you worked out in that other thread.  I'm still new at this stuff, so what I don't get is how the listofHolidays in the function gets plugged into the solution.  Is it a table?  A value list?  A global field with multiple values?  The overall solution I'm working will need to use this CF in several places, so I'll need to reference the listofHolidays numerous times.

Thanks for your patience while I try to understand all of this.

• ###### 5. Re: Date calc so end date doesn't fall on weekend or holiday
Most often, you'd have a table of Holidays and retrieve the list through a relationship to that table (using the List() function). The relationship can use either the x relational operator (i.e. ALL dates in the Holidays table) or restrict the range, for example to > StartDate.

You could also define a value list of the holiday dates, and retrieve it through the ValueListItems() function - that might be more convenient if you need it in several places.
• ###### 6. Re: Date calc so end date doesn't fall on weekend or holiday
I worked up a table as you described and it works perfectly.  Thanks a ton for your help.  Happy holidays to you.