subtracting dates

[sbod]

hi everyone i am new to filemaker but have put together some simple databases. all i want is some guidance (or the best case senerio a solution)in how to start solving this problem i am about to state. i am keeping track of start dates and end dates of a court trial. i get a total number by simply subtracting the start date from end date (or visa versa). my problem is how to not count saturdays and sundays if the trial overlaps into the next week. also while im here how to eliminate courting holidays if they should fall during the week being counted

[John Mark Osborne]

The following formula will work as long as the Start Date and End Date are both weekday dates:

Div((End Date - Start Date); 7) * 5 +

Mod(End Date - Start Date; 7) -

Case(DayOfWeek(End Date) < DayOfWeek(Start Date); 2; 0)

[comment]

I would think that if a trial started on say May 4th, and ended on the same day, then the trial lasted one day - not zero days. So perhaps it should be:

Let (

days = EndDate - StartDate + 1

;

Div ( days ; 7 ) * 5 +

Mod ( days ; 7 ) -

( DayOfWeek ( EndDate + 1 ) < DayOfWeek ( StartDate ) ) * 2

)

See here for a more general formula, that also allows any pattern of work days within the week.

Regarding holidays, you will need a table of those, and a relationship to include those holidays that fall within the range. Then subtract the count of related holidays (make sure to exclude holidays that fall on weekends from the relationship).

[sbod]

thanks for the quick response i will give it a go

[sbod]

thanks for the reply i will try it out

[sbod]

thanks again it worked (not that i had my doubts)good looking out about the trial beginning and ending on the same day

[sbod]

thanks also to you john for your quick response--this is a great site

[comment]

the trial beginning and ending on the same day

That was only an example - there is a constant difference of 1 day between the calcs, because one includes the ending date in the count, and the other doesn't.