I have an order date and order sent date.

I have a report where I am calculating what is the time lapse between order date and order sent date.

My calculation simply says 'OrderSentDate' - 'OrderDate'. All works fine.

Now, this calculation also adds weekends days as well. Is there a way in way in FileMaker I can exclude the weekend. So time lapse would just be based on weekdays number.

How would my new calculation be like.

Many Thanks.

Here's one way:

Let ( [

workStart = StartDate + Choose ( Mod ( StartDate - 1 ; 7 ) - 5 ; 2 ; 1 ) ;

n = EndDate - workStart + 1 ;

w = Div ( n ; 7 ) ;

r = Mod ( n ; 7 ) ;

d = Mod ( workStart - 1 ; 7 ) ;

a = ( d + r > 5 ) + ( d + r > 6 )

] ;

5 * w + r - a

)

This is assuming your weekends are Saturdays and Sundays.

Here's another:

Let ( [

s = Div ( StartDate ; 7 ) ;

e = Div ( EndDate ; 7 ) ;

w = e - s

] ;

EndDate - StartDate + 1 - 2 * w

)

This one works under the same assumption, and also requires StartDate and EndDate to be workdays.

See also: FileMaker Custom Function: WeekDaysInRange ( startDate ; endDate ; weekDays )