Date Calculation Ignoring Weekends

[Baylah]

Good Morning Everyone:

Can someone help me write a calculation (If Possible) whereby when a record is created the creation date is auto entered in the field called "order Entered" and then in the field named "Order Due" The calculation figures out date "order entered = order entered + 5" (easy, then the tricky part for me) but if that "order due" date falls on a Saturday or Sunday the calculation pushes that date to the next Monday?

This would be very cool and helpful to this data base I am writing and give a much more polished solution for the person I am writing this for.

Thanks in advance for any help you all can provide.

As an aside, is the a way that if someone responds to a post I make here, can I get e-mailed with that fact? Other forums I subscribe to have that functionality, but I can;t seem to find it here, although I am sure it exists.

I have signed up for the paid subscription to this forum as it is such a wonderful resource.

Thanks to everyone!

Steve

[email protected]

[transpower]

From FMI's Calculations and Scripts file:

StartDate + Int (WorkDays/5) * 7 +

Middle ("12345012340123601256014560345623456",

(DayOfWeek (StartDate) - 1) * 5 +

Mod (WorkDays,5) + 1, 1)

Notes (from FMI):

Given a starting date and a number of working days, an ending date can be calculated. This calculation assumes your two fields are named StartDate (date field) and WorkDays (number field), and there are five working days per week.

Here's the logic:

1. Since there are five working days per week, convert each five working days into seven actual days.

2. For those extra days that do not make up a group of five, add them to the starting date.

3. If the ending date falls on either a weekend or across a weekend, add two more days.

From logic step #1, use the Int() function, dividing by five, to determine the number of five day multiples, or actual weeks. With this result, multiply by seven to get the number of actual days. That is,

Int (WorkDays/5) * 7

From step #2, use the Mod() function to determine the excess days over the five day multiples. That is,

Mod (WorkDays,5)

The difficult step is logic step #3 - determining if the excess days takes us across a weekend. For example, if the Starting Date is a Friday, and one day is added, then somehow, we want to return the following Monday.

The chart below will help us determine the ending day of the week given the Starting day of the week (left column) and the the number of excess work days over the five day multiple (top row).

Work Days 0 1 2 3 4

Sunday M Tu W Th F

Monday M Tu W Th F

Tuesday Tu W Th F M

Wednesday W Th F M Tu

Thursday Th F M Tu W

Friday F M Tu W Th

Saturday M Tu W Th F

Sunday and Saturday are included in case StartDate is accidentally entered as a weekend date. That is, if the StartDate is a Sunday, and the excess multiple is zero, we don't want to return Sunday, but the following Monday. Using the chart, for a StartDate of Wednesday and three excess days brings us to the following Monday.

Now, change the table so that the number of actual days replaces the day of the week:

Work Days 0 1 2 3 4

Sunday 1 2 3 4 5

Monday 0 1 2 3 4

Tuesday 0 1 2 3 6

Wednesday 0 1 2 5 6

Thursday 0 1 4 5 6

Friday 0 3 4 5 6

To determine the day of the week for StartDate, we can use the Mod() function (with 7) and subtract a known Sunday date from StartDate (The first known Sunday date in the Macintosh system is January 3, 1904). This results in a value from zero (Sunday) through six (Saturday). If the result is Wednesday (value returned is 3), somehow, we want to drop down to the Wednesday row. Then, determine the number of excess work days above a multiple of five and move across to obtain the appropriate number of actual days.

This can all be accomplished by putting the entire table into one string, one row following the next. That is,

"12345012340123601256014560345623456"

To find the correct grouping, first find the day of the week that StartDate occurs, and then multiply by five (since each grouping contains five values - zero through four).

Mod (StartDate - Date (1, 3, 1904), 7) * 5

Now, find the excess days above a multiple of 5 (from logic step #2 above)

Mod (WorkDays, 5)

Now to obtain the correct value, add one to all of this to obtain the appropriate value. For example, if StartDate is a Sunday (returns zero from first Mod() function), and excess days is zero, it leaves us at the beginning of the string. Therefore, add one to get us to the first position.

[-Queue-]

Or just use StartDate + 5 + Choose( DayOfWeek(StartDate) - 2; 2; 1 ).

[MoonShadow]

Gotta love it. You rock, JT.

[Baylah]

AS always, This forum is awesome. Thank you guys! The simple calculation worked perfectly.

Steve

[transpower]

Maybe -Queue- should go to work for FMI and suggest improvements in the Calculations and Scripts file!

[-Queue-]

Nah, then I wouldn't rock in the forums.