# subtracting dates

This topic is 5687 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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)

##### Share on other sites

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).

##### Share on other sites

thanks for the quick response i will give it a go

##### Share on other sites

thanks for the reply i will try it out

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

This topic is 5687 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account

×
×
• Create New...