Are you sure that you want to use that formula? What happens if there is a holiday in that date interval?
There is another option that works from a table of "class dates" so that managing holidays is simply a data entry task and does not require redefining a calculation. While some use such a table in combination with a formula like what you've posted, I prefer to treat weekends as holidays and just work completely from the set of class date records in the table as that produces a simpler calculation.
Div((End Date - Start Date); 7) * 5 +
Mod(End Date - Start Date; 7) -
Case(DayofWeek(End Date) < DayofWeek(Start Date); 2; 0)
I'm not sure I completely understand, could you show me an example please?
I think I explained that wrong. What I meant was if someone puts in Monday May 8th, and then in the next field puts in 10 days, the End Date field should calculate Friday, May 26th.
May 26th or May 19th?
If you are wanting May 19th as the result (rather than May 22nd with your version) then this modification to your formula seems to do the job:
StartDate = StartDate - Case(DayOfWeek(StartDate) = 2; 3; 1);
StartDate + Int ( WorkDays / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek ( StartDate ) - 1 ) * 5 + Mod ( WorkDays ; 5 ) + 1 ; 1 )
Set up a table with one record for each day on which class can be held. You only need a date field in this table to record the day that it represents. For a holiday, you can find the record for that date and delete it.
Relate it by date to your layout's table occurrence:
LayoutTO::startDate <= ClassCalendar::Date
GetNthRecord ( ClassCalendar::Date ; NumberOfDays)
will then give give you the last day of class.
I've described the simplest possible set up. I actually had a table with one record for every day of the year and set a field value to mark which days were "open". My relationship matched by date and a status field so that it only linked to open dates.
thanks to both philmodjunk and Jaymo,
I used both of your answers which gave me the correct number of days and the correct day of the week. Then I added the Holiday table as described and it all works great.
You are most welcome. Happy FileMaking!