Adding working days to a date 1

[neilmcdonald]

Hi,

I'm sure there must be a way to do this, but I cannot figure it out! I need to be able to add a number of working days to a date. I can find numerous examples of how to calculate the number of working days between two dates, but no way of adding them!

e.g. A customer logs a call with us today (01.12.03). the fix time for that particular call may be 2 working days. I need a formula which will add 2 working days to the call log date.

Any help would be much appreciated!

 

[bdreed35]

if you add an integer to a date, it assumes the integer is days and adds them appropriately to the date:

{table.call_log_date} + 2

You need to replace the 2 with the formula, variable, or database field that is holding the number of working days.

An alternative solution is to use the DateAdd function:

DateAdd("d",2,{table.call_log_date})

~Brian

 

[neilmcdonald]

Thanks Brian - the problem I have is how to add only working days. e.g. if the call was logged last friday, with a response time of 4 days, then the response date is the following thursday, as the weekend is not counted. Do you have any idea how I can get the formula to ignore weekends (and ideally public holidays!)?

 

[dgillz]

Ken Hamady has a real good discussion regarding this on his website:

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

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

http://www.trianglepartners.com

 

[bdreed35]

Here is another thread that posted some solutions: thread767-669786

I would take a look at that first. Getting rid of weekends is fairly easy. The diffucilut part would be the holidays as these are different for every country/company.

~Brian

 

[neilmcdonald]

thanks again guys - feel free to call me stupid, but I can't figure out how to translate the formulas for working out the number of working days between two dates into a formula which adds a number of working days to a start date. Am I being thick?

 

[Reebo99]

This should work for you:

Local DateVar StartDate := Date(2003,11,27);
Local NumberVar NetDays := 5;
Local NumberVar Counter := 0;

While Counter <> NetDays do
(if Dayofweek(StartDate+Counter) in [1,7] or
StartDate+Counter in [Date(2003,12,02),Date(2003,12,03)] then NetDays := NetDays + 1;
Counter := Counter +1;

StartDate+NetDays;

Replace items in bold with your fields and items in italics with your holiday days.....

Reebo
UK

 

[synapsevampire]

The long term solution is to construct a periods table.

Tis is standard fare i a data warehouse for precisely this reason, try to avoid hardcoding hacks.

A period table contains all dates, with meaningful information such as weekend, holiday, business hours, fiscal period, etc.

I've posted a SQL Server script here before to help people construct one, and there are lots of them out on the web for every database.

Or you might even check the CD site for more eleaborate versions of the solutions offered here (lots of whitepapers), but again, it's short term thinking, lacking forethought of maintenance and reusability.

-k