Calculating a Future Business / Work Date

[CanWest]

I am trying to find a simple way to determine the work date based on knowing the Start date and how many days ahead I want the date for. These means I need to exclude weekends and holidays.

I tried Pat Hartmans solution located at

http://www.access-programmers.co.uk/forums/showthread.php?t=238821

I discovered that the function in there that is supposed to do this does not account for weekends, only holidays.

Does anyone have any suggestions

 

[CJ_London]

Have a look at this link

http://support.microsoft.com/kb/115489

which should resolve your weekend problem - the result can then be adjusted to take into account holidays

 

[CanWest]

Have a look at this link

http://support.microsoft.com/kb/115489

which should resolve your weekend problem - the result can then be adjusted to take into account holidays

Thankyou for this.

However I do not have a clue on how to account for the holidays in this example.

It seems I have one that does holidays and another that does weekends with no idea how to merge them

 

[CJ_London]

Which of Pat's functions are you using? 1 or 2?

 

[CanWest]

Which of Pat's functions are you using? 1 or 2?

On the Business Days tab I am using the function that is on the bottom

 

[CJ_London]

I'm a bit concerned - are you using the db provided by Pat to calculate the date or have you learned from what Pat has done and tried to adapt it to your own needs?

If the former, then I cannot really help, this is something that Pat has written and I'm having to guess which bit you are having problems with.

I'm guessing that this is a function called CalcEndDate and I can see two possible reasons why this might not be working as you expected.

1. StartDate = DateAdd("d", StartDate, 2) - I think this should be StartDate = DateAdd("d", 2, StartDate) but I don't know if the fact it is the wrong way round will make a difference
2. This function only moves the end date forward if the start date and/or end date fall on a saturday or sunday - if you are adding say 30 days then there will be more saturdays and sundays to be added. I don't know if this is by design or not - there is a warning it has not been tested.

If the latter, please provide the code you are using

 

[CanWest]

I'm guessing that this is a function called CalcEndDate and I can see two possible reasons why this might not be working as you expected.

2. This function only moves the end date forward if the start date and/or end date fall on a saturday or sunday - if you are adding say 30 days then there will be more saturdays and sundays to be added. I don't know if this is by design or not - there is a warning it has not been tested.

I'm sorry I wasn't clear. I have not actually implemented any code yet. I was simply testing what Pat provided to see if it would work for me. That is when I discovered that it did not seem to work as I thought it would. My understanding is that given a startdade of say Sep 12, 2013 and the number of business days of 5 that the resulting date would not count Saturday and Sunday and should be Sep 18, 2013. The actual date it produces is Sep 17, 2013 which is only 4 business days but 6 days in total.

I do understand that it says not tested yet. I was hoping that someone with more expertise than myself could help as I am still very new to all this,

Many thanks

 

[CJ_London]

I accept you need to start somewhere but I suggest you make a start on trying to get the function to work as you require, I can point you in the right diection and make suggestions, but regret I do not have the time do this for you.

This link provides an alternative solution

http://www.access-programmers.co.uk/forums/showthread.php?p=1272470#post1272470

and if you look online you will find plenty of alternative suggestions.

It does not allow for holidays which Pats does so may not meet your requirements. Alternatively if you look at the other calculation on that tab, it appears to calculate the number of days correctly so you can probably modify that code to get the code you want working correctly.