calculate due date

[Rokrz]

Hi All,

Does anyone have an expression that can be used to calculate a due date from a base date excluding Saturdays and Sundays in the count. I am trying to set due dates ie "date + 2 workdays", "date + 7 work days" etc.

Also I woiuld like to have exclusion dates for holidays etc.

Thanks,

Bob Okrzynski

[David Sander]

RE: calculate due date

Bob,

This can be done I suspect in the following way with Alpha's Date calculations. Pardon my lack of precise coding practise too.

The first thing you need to do is to use the DOW or Day Of Week function, see (2). Place the current DATE in the DOW function which always gives you Sunday, next add one day to get monday. From monday, all your business weeks are five days long. Next, use the INT() or Integer function to clean up the weeks calculation. For 21 days (1) this would be INT(21/5)*7 + (21-(INT(21/5)*5)which would equal 29 calendar days from the prior Monday. Replace each 21 with the numberic business days variable you use.

To this calculation you would add the number of days ahead of Monday where you are in the week to get the calendar date needed. With some algebra to put all the calculations together, this should be all that you need.
(2) Date()-DOW(Date())+1 = prior Monday
(3) DOW(Date())-1 = Days from Monday for today

So the result is: (2)+(1)+(3) = the calendar date

This doesn't cover business holidays, but maybe the users could deal with that. To calculate a business holiday you need to know that the holiday is greater than today and less than the calendar day, then insert an extra day. But it's bad enough that I'm making this up as I go along. :)

Dave

[Randy Stewart]

RE: calculate due date

first off I am not a great programmer, but we have done something simular, but by building a database for a reference call bizday. Fields in database are bizdate(actuall date of business), biznumb(consecutive number of day or record number in this database), first I went in and did a 365 records and auto advanced for each day of the week, then deleted all the saturdays and sundays. then figured out the days that were to be recognized holidays for the year, so I was left with only the days that applied to "working business", and those I numbered concecutively.

Now I do a lookup on field for business today field called bizdate = xlookup('bizday.dbf','date',DATEPURCH,'biznumb')+10; this is the business day of the year + 10 days, now for duedate = xlookup('bizday.dbf','date',bizdate,'bizdate') which gives the business day it will be due. if you want a sample of this I can send it... but it was the only way we could get what you are talking about done.