Formula to Return Future Date 1

[jpstrzoch]

Greetings,

I am an amateur Crystal Reports user. I manage a box printing operation in Chicago, IL. The Chicago, IL location is known systemically as WHSE-101. Box orders are placed that are relational to geographical locations where we have additional warehouse locations. Example, Iowa, WHSE-301, Indianapolis, WHSE-201, Cincinnati, WHSE 701, etc. There are weekly wareshouse transfers from Chicago to Iowa on Monday's, from Chicago to Indianapolis on Wednesday's, and from Chicago to Cincinnati on Thursday's.

My report has a formula which returns a date value called {@Target Date}. This is the date that the order must be printed by.
My report has a DateVar Array that lists the holidays called {@Enter Holidays}. This is simply a list of our company recognized holidays thus we are closed.
My report has a Number field that lists the warehouse number relational to the order called {BOXPRNT_.WHSE}. This is where the finished boxes will ultimately be transfered to.

I need to create a formula that returns a date value, {@Transfer Date}, which would be the next viable warehouse transfer date relational to 301, 201, and 701, ultimately the very next Monday, Wednesday, or Thursday respectively. I need it to start from the {@Target Date} first, and identify the next relational date, and if the date returned just happens to fall on a company holiday, {@Enter Holidays}, then it should bounce forward to the following week's Monday, Wednesday, or Thursday.

Can you help me please? I have no idea where to start.

Thank you in advance for your time and expertise!

 

[hilfy]

The challenge with this is that the day of the week for transfer to each warehouse is not in your data. So, you're going to have to hardcode for it or find a way to get it into your data.
You might do something like this:

Numbervar addDays := 0;
Numbervar whseDOW := 
    Switch(
      {BOXPRNT_.WHSE} = 301, 2,
      {BOXPRNT_.WHSE} = 201, 4,
      {BOXPRNT_.WHSE} = 701, 5
    };  //add all of the warehouses to this.  Assumes Sunday is the first day of the week.
addDays := 7 - DayOfWeek({BOXPRNT_.WHSE}) + whseDOW;

if ({@Target Date} + addDays) in {@Enter Holidays} then addDays := addDays + 7;
{@Target Date} + addDays;

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com