Calculate 7 day Ship Date

[jill]

I'm trying to set up a calculation to auto-enter a shipping date that is 7 days from day of entry, excluding weekends. I've gotten it work except if the day you are entering is a thursday or friday. Since it spans two weekends, it doesn't exclude that second weekend. Here's the calculation I'm using, which I pulled from FMPro's site:

Creation Date + shipping days + Int(shipping days/5)*2 + If(Mod(shipping days,5) +DayofWeek(shipping days)=7 or Mod(shipping days,5) + DayofWeek(Creation Date)=8, 2, 0)

My "shipping days" field is a number which auto-enters "7".

Help anyone?

Version: v6.x

Platform: Mac OS X Jaguar

[-Queue-]

Another way to go about it would be to use

CreationDate + 9 + ( DayofWeek( CreationDate ) = 5 or DayofWeek( CreationDate ) = 6 ) * 2

since you're always adding 9 days unless it's Thursday or Friday, in which case you add 11.

[CyborgSam]

Jill->

Welcome to our community.

Here's a calc that will give you the Date the shipment should be received for ANY number of Shipping Days:

CreationDate + ShippingDays + (2 * ( Int((ShippingDays + 4) / 5) - ( (DayofWeek(CreationDate) + Mod(ShippingDays, 5)) <= 6 )))

How it works: CreationDate + ShippingDays is obvious, but the weekend days need to be added. Each weekend is two days: 2 *.

The max # of possible weekends for a # ShippingDays is Int((ShippingDays + 4) / 5). Any multiple of 5 can run over to an additional weekend from the lower #s.

((DayofWeek(CreationDate) + Mod(ShippingDays, 5)) <= 6 ) This is the test to see if the nth possible weekend does not occur. 0 means it occurs, use the max # weekends. 1 means it does not occur, subtract the last weekend.

Does this make sense?