×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

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

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

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

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

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

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


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

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

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

(OP)
LB,

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

Thanks much.

-BigdaddyPrecision

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

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: Common Formula Examples.

-LB

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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