Working Days From Todays Date

[TimMoffy]

Hope someone can help me and that i'm in the correct forum for this.
I have created a form that captures the date on which a customer makes contact wiht us. As part of our regulatory requirements we are required to acknowledge this contact within 5 days.

So i have a field called [dtmDateRecieved]. What i am trying to work out is how to calculate 5 workings day forward from this date. in other words if today is the 26/3/2013, then what will the date be in five working days time, ignoring public and bank holidays? I know how to do it in Excel as a formula but that doesnt seem to work in Access.



I have found lots of articles of how to calculate the number of working days between two dates, but not counting forward from a fixed point like this.

Many thanks in advance for your help

[rpeare]

Assuming your initial contact happens on the workweek (m-f):

IIf(Weekday(DateAdd("d", 4, contactdate)) = 1, DateAdd("d", 5, contactdate), IIf(Weekday(DateAdd("d", 4, contactdate)) = 7, DateAdd("d", 6, contactdate), DateAdd("d", 4, contactdate)))

The formula would have to change if your initial contact can be on a weekend as well.

IIf(Weekday(DateAdd("d", 4, contactdate)) = 1 Or Weekday(contactdate) = 1, DateAdd("d", 5, contactdate), IIf(Weekday(DateAdd("d", 4, contactdate)) = 7 Or Weekday(contactdate) = 7, DateAdd("d", 6, contactdate), DateAdd("d", 4, contactdate)))

[TimMoffy]

We only work M-F so the first part works well. Many many thanks for your hlep. I'd never have worked that out on my own.