subtract number from date to get start date 1

[bspalmer]

I need to find a startdate that excludes weekends.
I have the duedate and the leadtime and I know how to subtract the leadtime from the duedate to give me the startdate, by using the formula below, but I need it to exclude the weedends...

DateAdd ("D",-{leadtime},{duedate})

So, if the duedate is 5/13/2005, and the leadtime is 10days, then the start date should be 4/29/2005. The formula above gives me a startdate of 5/3/2005.

Thanks

 

[goodprg]

May be this will help -

Local DateTimeVar d1 := {Orders.Order Date};
Local DateTimeVar d2 := {Orders.Ship Date};

DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)

HTH

 

[bspalmer]

The problem I'm having with the DateDiff formula is that d1 is a number, not a date field.

I basically need a formula or multiple formulas to do the following:

(5/13/2005 - 10) (exclude Saturday and Sunday) = 4/29/2005

Thanks

 

[goodprg]

you can add 10 days to your start date and get a second date. Then you can use the DateDiff Formula.

Thanks,

 

[bspalmer]

I guess I don't know what you mean...

I'm trying to find the start date, so how can I add 10 days to it...

Here's what I know:
Duedate = Date
Leadtime = Number

Trying to find the start date, which equals the duedate - leadtime, but it has to exclude any Saturdays and Sundays.

Thanks

 

[bspalmer]

Any other ideas on this...

I could really use the help

Thanks

 

[dgillz]

D1 has to be a number for datediff() to work. What does d1 represent? The number of days?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[bspalmer]

Yes, in the formula above, d1 would represent the number of days it takes to build the part...
and d2 would represent the date the part is due to ship...

So I need to find the date they need to start building the part...start date, excluding weekends,

When I use the datediff() formula above, Crystal says a date is required in d1

Thanks

 

[dgillz]

Try Ken Hamady's formula for adding/subtracting a number of business days:

http://www.kenhamady.com/form17.html

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kskid]

If Crystal says a date is required in d1, that usually means that your {orders.date} field is either null or not a date field. You will either have to format it or substitute another date for the null

-LW

 

[rosemaryl]

Dear bspalmer;

I think something like this would work.

It will subtract from the the proposed start date (pd) the number of days that equal the weekend dates in the range... I didn't have time to extensively test ... but I think this is the direction you want to take:


//beging formula

datetimevar pd := DateAdd("d",-{Table.leadtime},{Table.DueDate});

datetimevar sd :=

pd - (datediff("ww",pd,{Table.DueDate},crsaturday))
- (datediff("ww",pd,{Table.DueDate},crsunday));

sd
//end formula

I think it would have to be further tested to see if the resulting (SD) itself ends up on a weekend date and then move it back to the business day before that ...I didn't have time to go that far.



I hope that helps,

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear bspalmer,

Well I had time after all ...

Here it is with a check to see of the sd ends up on a weekend:

atetimevar pd := DateAdd("d",-10,{Incident.Close Date & Time});

datetimevar sd :=

pd - (datediff("ww",pd,{Incident.Open Date & Time},crsaturday))
- (datediff("ww",pd,{Incident.Open Date & Time},crsunday));

datetimevar TD :=
(
select dayofweek(sd)
case 1 : sd-2
case 2 to 6 : sd
case 7 : sd-1
);

td

I think that will work..

Best regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[bspalmer]

Rosemary,

Your formula is so close, it works for some dates, but not for all, which makes no sense to me, maybe you can figure out why...

Here is how I have the formula:
//begining formula

datetimevar pd := DateAdd("d",-{Part Master.MFGLT_01},{Order Master.CURDUE_10});

datetimevar sd :=

pd-(datediff("ww",pd,{Order Master.CURDUE_10},crSaturday))
-(datediff("ww",pd,{Order Master.CURDUE_10},crSunday));

sd;

datetimevar td :=
(select dayofweek(sd)
case 1 :sd-2
case 2 to 6:sd
case 7 :sd-1
td);

//end formula

Did I write it correctly???

Here are some dates that it didn't work for
Curdue date = 5/25/2005
Leadtime = 10 days
formula gave me a start date of 5/13/2005
should be 5/11/2005

Curdue = 5/24/2005
Leadtime = 10 days
formula gave me startdate of 5/11/2005
should be 5/10/2005

Curdue = 5/26/2005
Leadtime = 5 days
formula gave me startdate of 5/20/2005
should be 5/19/2005

I just talked with one of our planners, you don't count the Curdue date as part of the leadtime, so maybe if you add 1 to the curdue date, but that will mess up the ones that are correct...

I don't know, guess I will just keep plugging away at it,

Thanks for everyones help...

 

[rosemaryl]

DearBspalmer,

I thought it might need some refining ...

I think I know what the issue is. I will work on it a bit and then post what I come up with.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Bspalmer:

If you look at the help on datediff "ww" interval, you will see that if the startdate falls on a saturday or sunday then it doesn't include the startdate as one of the dates returned by the ww difference. I knew this and had forgotten about it ... sorry.

So, I think this solves the issue:

//begin formula

datevar pd := cdate(DateAdd("d",-10,cdate(2005,5,24))); //Proposed Date

datevar sd :=  //scheduled Date

 pd - (datediff("ww",pd,cdate(2005,5,24),crsaturday))
     - (datediff("ww",pd,cdate(2005,5,24),crsunday));

(select  dayofweek(pd) 

case 1      : sd - 2
case 2 to 6 : sd
case 7      : sd - 1
);

datevar TD :=   //True Date to correct for a Scheduled Date Landing on Weekend
(

select dayofweek(sd)

case 1      : sd - 2
case 2 to 6 : sd
case 7      : sd - 1
);

td
//end formula

This works for all the test dates you provided, but I didn't have examples from you of dates that were already working, so hopefully it doesn't break for those

Regards,

ro
td

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[dgillz]

BSpalmer you owe Ro a star on this one for sure!

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[bspalmer]

Rosemary,

I thank you so much for all the help on this and I give you 5 stars for sure...

But still having problems, I copied and pasted the formula so it was exactly how you wrote it, but I got a ) missing when I check the formula...when I click ok it put the cursor under the //end formula

On the first formula it did the same thing, but I just moved the tb before the last );

I tried that but didn't work, after adding another ) to the end of the formula, then I got a date-time needed where I had the curdue date, must be the cdate formula dosen't like the date format of CurDue, so I modified the formula as shown below, which resolved the errors, but still gives me the wrong start date...

Maybe it just wasn't meet to be...

//begin formula

datetimevar dd := {Order Master.CURDUE_10};

datevar pd := cdate(DateAdd("d",-{Part Master.MFGLT_01},cdate(dd))); //Proposed Date

datevar sd := //scheduled Date

pd - (datediff("ww",pd,cdate(dd),crsaturday))
- (datediff("ww",pd,cdate(dd),crsunday));

(select dayofweek(pd)

case 1 : sd - 2
case 2 to 6 : sd
case 7 : sd - 1
);

datevar TD := //True Date to correct for a Scheduled Date Landing on Weekend
(

select dayofweek(sd)

case 1 : sd - 2
case 2 to 6 : sd
case 7 : sd - 1
td)

//end formula

 

[rosemaryl]

Dear BSpalmer,

What version of Crystal? I am going to guess CR 8.5 as there was a bug in that version when you had parenthesis and a Select Case formula. Basically, you have to add an extra paren at the end so you end up with mismatched numbers of closing parens... They fixed this in CR10. This is one reason that it is important to provide the version of Crystal that you are using!

Here it is written in CR 8.5 ... try this one and then if it still isn't right as to the dates, give me dates and lead times that it works for and does not work for ... I need data to test on.

Try this one exactly as it is except for changing the field names (you would replace CDATE(2005,5,24) with your datefield not with CDate({Table.DateField}), I just used that in testing your dates, I should have been more explicit:

datevar pd := cdate(DateAdd("d",-10,cdate(2005,5,24))); //Proposed Date

datevar sd :=  //scheduled Date

 pd - (datediff("ww",pd,cdate(2005,5,24),crsaturday))
     - (datediff("ww",pd,cdate(2005,5,24),crsunday));

(select  dayofweek(pd) 

case 1      : sd - 2
case 2 to 6 : sd
case 7      : sd - 1
);

datevar TD :=   //True Date to correct for a Scheduled Date Landing on Weekend
(

select dayofweek(sd)

case 1      : sd - 2
case 2 to 6 : sd
case 7      : sd - 1
);

td

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[bspalmer]

Ro,

I am so sorry for not giving the Crystal version, yes I am using 8.5

Using the last formula, here's what shows on the report,
the first four give wrong Start Dates, last five give the correct start dates:

LT Start Date Due Date
10 5/13/2005 5/25/2005
10 5/4/2005 5/17/2005
10 4/22/2005 5/5/2005
5 5/20/2005 5/26/2005

10 4/22/2005 5/6/2005
5 5/4/2005 5/11/2005
5 4/26/2005 5/3/2005
5 5/4/2005 5/11/2005
5 5/10/2005 5/17/2005

Thanks

 

[rosemaryl]

Dear baspalmer,

That is good information, could you also give me the date you want it to be?

That would help, I will have to work on this tomorrow as I have to get something done for a client. We will get there ...I am missing something...

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762