Auto Calculate Deadline Date excluding Weekends (1 Viewer)

Hi There,

It's been a little while since I have posted on here so hopefully someone might be able to help me with my predicament.

I am trying to create a Complaints Tracker to ensure we keep to our SLAs.

I want the database to return a 'Deadline' date for feedback to customer but based on the Category and taking into account Weekends.

I have created an expression to calculate this within a query, but it doesn't capture weekends. My current expression is;

Deadline: IIf(IsNull([Category]),"",IIf([Category]="Category 1",[Date Received]+2,IIf([Category]="Category 2",[Date Received]+4,IIf([Category]="Category 3",[Date Received]+6,0))))

I've been looking all over the internet but unsure what search term to use, everything I find is about datediff but I don't believe this function would be suffice for what Im after......help! haha!

Any advice would be much appreciated. I'm getting better with VBA now so if that's a better route, more than happy take advice

Kind Regards
Stuart

There are many threads on this topic if you use the forum search feature.
Start by looking at the similar threads listed at the bottom of this page

Simply create a function in a module then call it in the query:

On your query:

Deadline: calcDeadline([category], [date received])

arnelgp said:

Simply create a function in a module then call it in the query:

Code:Copy to clipboard

Public function calcDeadline(cat as string, byval  dteReceived as date)
Dim i as integer

Select case cat & ""
Case is = "category 1"
I=2
Case is = "category 2"
I=4
case is ="category 3"
I=6
end select
If i > 0 then
While i >0
dteReceived=dateadd("d", 1, dteReceived)
If instr("SatSun", format(dteReceived, "ddd"))=0 then
I =i - 1
End if
Wend
calcDeadline=dteReceived
End if
End function

On your query:

Deadline: calcDeadline([category], [date received])

Click to expand...

Hi arnelgp,

Thank you so much, that has worked a treat. I don't want to be a pain, but are you able to explain the VBA above so hopefully I can learn from it going forward? I don't want to be one to keep asking for solutions aha

All the best
Stuart

It is very straight forward. you pass the category and date received to the function.
The function chk whether cat 1 or cat 2, etc. Then set i (number of days to add).
After adding 1 dat to date recv, it chk if it is saturday ir sunday. If it is not, variable i is decremented.

You will noticed that the result is left aligned. You can make it right aligned by going to design view, click on the calculated column (down panel). And on its property (right panel), Format, put mm/dd/yyyy (w/o quote).

At some point you might realize that you probably need to avoid holidays also. Here is a sample database with multiple useful date functions. To use the holidays feature, you will need to populate the holiday table with YOUR holidays.