How to exclude specific days of the week (Friday, Saturday, Sunday)

[BigdaddyPrecision]

Hey everyone,

First off I am relatively new to Crystal Reporting and have taken classes and been part time active in it for the past 1.5 years. I don't have an IT background and have decided to learn this system to support my employees and my customers better. Just so you have a medium level understanding in my formula writing expertise.

I am looking to have my SAP crystal report perform a formula where it is subtracting a specific number of days from a field if criteria is met accordingly. What I need help with is how do I skip specific days of the week because we do not schedule Friday, Saturday, or Sunday. What I am trying to accomplish is when the formula is run and returns the output/answer that will not land on a friday, saturday, or sunday.

Here is the current formula that does the math correctly but does not take into consideration these weekend days I am trying to avoid.

Thank you in advance for your help & consideration.

Formula:

if {Job_Operation.WC_Vendor} = ["MTLRECV", "HORIZONTAL"]
THEN
{Job_Operation.Sched_Start}-4

 

[lbass]

It would help to understand why you want to subtract 4 from the start date. You can use the dayofweek function (this isn't necessarily what you want to do, but it shows you how you can use the function):

If dayofweek({table.date})=1 then //Sunday
{table.date}-3 else
If dayofweek({table.date})=7 then //Saturday
{table.date}-2 else
If dayofweek({table.date})=6 then //Friday
{table.date}-1 else
{table.date} //use given date

The above formula would translate any date on Fri, Sat, or Sun to the previous Thurs.

-LB

 

[BigdaddyPrecision]

Thanks LB

I am subtracting 4 days from a start date to act as an indicator to another department to so called "get ready". This creates a list of items that will be ready to be worked on 4 days in advance of they being required for another much larger starting operation. I own a machine shop and this is just a way to get departments using the same information but all working collectively together so that stuff is ready to go.

It is the same platform they use in surgery, don't have a patient come in for surgery if you have to put them to sleep to begin your work only to wake them up 1/2 done because you forgot to plan in advance. We do this in manufacturing all the time and it needs to be handled more with a surgical mindset - time and quality of outcome is everything.

 

[BigdaddyPrecision]

LB,

I used your formula and it worked so thanks - I am just putting somethings together.

Thanks much.

-BigdaddyPrecision

 

[lbass]

So if you want to alert people four working days before the start date, and if the start date can never be on a Friday, Saturday, or Sunday, then just subtract 7 to allow 4 working days before the start date. This of course becomes complicated if you want to allow for holidays. Ken Hamady has a formula for work days that accommodates holidays -- see faq767-995.

-LB

 

[SkipVought]

Hi,

Just browsing forums I don't usually post in, and I noticed that you run a machine shop.

But my interest is in shop floor planning and scheduling (over the past 35 years or so at LTV/Vought, Northrup Grumman, Bell/Textron) and a brief portion of that latter span, using SAP.

Seems you are using at least one SAP module. Of course, they have their own database that's not easy to access apart from the user interface. But there ought to exist a calendar that has manufacturing days explicitly defined. Our IT department had a periodical Oracle snapshot of the SAP tables that could be used for purposed such as reporting pre-production requirements like cut/pick kits in preparation for machining or assembly. Actually the routing for any part, included time and instruction for pre-production, fabrication and post-production.

Bottom line related to this discussion: manufacturing and non-manufacturing days ought to be unambiguously specified in your corporate manufacturing calendar and not calculated! 4 days before the start of any manufacturing is simply [MANUFACTURING START DATE]-4, referencing your Manufacturing Calendar. You need access to that calendar table for your CR reports.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein