3 Replies Latest reply on Jan 10, 2015 11:04 AM by philmodjunk

# Calculating a Finish Date Given a Starting Date and the Number of Work Days

### Title

Calculating a Finish Date Given a Starting Date and the Number of Work Days

### Post

I am trying to determine an 'EndDate' that is 10 weekdays after a 'StartDate'. In addition to Saturdays and Sundays, I want to exclude known weekday holidays, which i have included in lookup tables. I have proved that the 'LookUp_High' and 'LookUp_Low' values are correct, however the calc is still not returning the correct date and provides weekend dates?

The following is where i'm up to at present:

StartDate + Int ( 10 / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek (StartDate) - 1 ) * 5 +  Mod (10 ; 5 ) + 1 ; 1 )- (Lookup_High - Lookup_Low)

In anticipation

Stuart

• ###### 1. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days

I recognize this function from a KnowledgeBase article where I once helped FileMaker fix a typo in the function.

But what about non weekend holidays? Will you need to adjust this count by allowing for closures on days other than Sat, Sun?

I ask that, because there is a completely different method that I've used that allows for closures on any day of the week--both regular and holiday closures.

• ###### 2. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days

Hi Phil,

I want the EndDate to be a weekday (Mon, Tue, Wed, Thu, Fri); however, i don't want to include known weekday bank holidays. i.e if the 10th day after my StartDate is on Easter Monday, i want the EndDate to be the following day (Tuesday).

Best Regards

Stuart

• ###### 3. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days

I will read that as "yes, I do need to account for holiday closures in addition to week end dates".

I worked on a DB where the State of California required a 3 day "hold" on certain transactions. We had to hold the customer's money for at least 3 business days before they returned to get their money from us. What I set up was a "calendar" table where I had one record for each day of the year and "marked" which records were for dates that we were open. I had a way to flag specific days of the week (we were open on Saturdays, closed on Sundays) as "always closed". I could pull up a calendar display of these dates and click a button on the days that represented a holiday closure.

I then set up a relationship similar to this:

MainTable::Date < Calendar::Date AND
constOpen = Calendar::Status

ConstOpen was a calculation field that returned the same value we set in the status field of Calendar to mark a record as a day that we were "Open".

Then we used this calculation to return a date that was 3 days into the future:

GetNthRecord ( Calendar::Date ; 3 )

In your case, you would put the field specifying the number of business days in place of the 3 that we needed.

We used a server scheduled script to keep adding new day records to the calendar table (we only needed a few weeks "lead" on the current date) And we had a "calendar layout" where we could use mouse clicks to control what days were marked as "open". That calendar layout was set up similar to this calendar demo file: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

This file is in the older file format so you will probably need to use Open from FileMaker's File menu to open the file and produce a copy converted to the .fmp12 file format.