×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula to Return Future Date

Formula to Return Future Date

Formula to Return Future Date

(OP)
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!

RE: Formula to Return Future Date

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:

CODE

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
www.protiviti.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close