Jump to content

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

Link to comment
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).

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.