Do you only need to exclude weekends or will you also need to exclude holidays?
Weekends can be allowed for via calculation, but Holidays require tracking via a table of holiday dates before you can allow for them in your calculation and that approach can be modified to treat weekends as holidays so your answer to this question determines which of two methods might be used to calculated your end date.
Yes, I really need to exclude holidays as well.
So if you set up a table of Holidays and other closures (weekends) with a date field to record the date that your business is closed, you can set up this relationship:
YourTable::StartDate < DatesClosed::Date AND
YourTable::constOpen = DatesClosedStatus
You can double click the relationship line to open up a dialog where you can change the = to an inequality operator. While you have that open, you can click the Sort check box for DatesClosed side of the relationship and specify that the records in this table be sorted by Date in ascending order.
You can create one record for each day of the year. (I used a server scheduled script to continually add new records and automatically mark Sunday dates as "closed" (We were open on Saturdays) ). Set the status field to Open for dates you are open and closed for dates that you are closed. A calendar format can be set up on a layout for such updates. constOpen can be defined as a calculation field that always evaluates as "open".
Then this calculation will compute the End Date:
GetNthRecord ( Date ; CalculatedField )
So if CalculatedField returns the number 5, you get the date of the 5th day after StartDate where you are open for business.
Thank you for your time!
Works great! I really appreciate your time!
Here's a calendar demo file that you might adapt to the purpose of managing which days you are open for business:
It's in the older file format so you'll need to use FileMaker's File menu to open the file to convert it to the newer file format.