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

 

[bspalmer]

Ro,

this is for the first four that are wrong

5/13/2005 should be 5/11/2005
5/4/2005 should be 5/3/2005
4/22/2005 should be 4/21/2005
5/20/2005 should be 5/19/2005

If you look at a work calendar Monday thur Friday, it make it easier to figure out the start date

Apri 2005
Mon Tue Wed Thur Fri
1
4 5 6 7 8
11 12 13 14 15
18 19 20 21 22
25 26 27 28 29

May 2005
Mon Tue Wed Thur Fri
2 3 4 5 6
9 10 11 12 13
16 17 18 19 20
23 24 25 26 27
30 31

So, for example if you take the first one with a due date of 5/25/2005 with a 10 days leadtime, start on the 25th and go up two weeks gives you a start date of the 11th, if the leadtime was 5 days, go up one week to the 18th...

Hope this helps
bp

 

[rosemaryl]

Dear BSPalmer,

Yeah .. I knew if I looked at a calendar ... but I was being lazy... and wanted to verify the dates that you thought they should be. I will work on it tomorrow for you.

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]

I'm guessing that this is a lost cause...

Ro, thank you for all the help & time you've given me on this...

bsp

 

[rosemaryl]

Dear BSpalmer,

No. I'm sorry I just got swamped. I am teaching a Crystal Bootcamp for a software vendor's VARS in Dallas this week and I just haven't had time to work on it.

It is not a lost cause, I just need some time to work on it. My apologies on the delay ...

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,

It's been a while, have you had a chance to work on this...

Thanks
bsp

 

[rosemaryl]

Dear BSPalmer,

I am so sorry for the delay. I have been on two weeks of business trips.

This is corrected code and my testing shows it works!

Let me know if you have any problems with it ... and I will rework it. But I think this is a go.

datevar dd := cdate(2005,6,01);

dd := if dayofweek(dd) = 1 then dd  -2
               else if dayofweek(dd) = 7 then dd - 1
               else dd;

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

datevar sd :=  //get to schedule date

pd - datediff("ww",pd,dd,crsaturday)
    - datediff("ww",pd,dd,crsunday)
;
sd := if dayofweek(pd) = 7 then sd -1
          else if dayofweek(pd) = 1 then sd -2
          else sd;

datevar TD :=   //True Date - correct for sd on weekend, shouldn't happen but doesn't hurt to test!
(

select dayofweek(sd)

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

td

It can probably be streamlined and more elegant, but it works.

Best regards and thanks for your patience!

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