Query expression Due Date -2 not counting weekends (1 Viewer)

[Hydra427]

Hello and Thanks for taking a look. I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday. Any help you can give me will be greatly appreciated.

IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))

Thanks, John

 

[Guus2005]

IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))

From your text: So when the due date is on a Tuesday, you want this statement to return Tuesday-4=Friday and if due date is not on a tuesday you want it to return DueDate-2 ?

Looking at the Weekday function it assumes that Sunday is the first day of the week. That is the default.
If you want it to be Tuesday the complete statement should look like this:

IIf(Weekday([Due Date],vbMonday)=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))

HTH

 

[Hydra427]

Guus2005, A quick question here. I have tried your solution and it work fine, just curious what the vb in front of Monday does in the statement.

 

[Guus2005]

hi.
vbMonday is a constant.
When you type control-g, your in the immediate window. In this window you can type commands and look what the result is. When you type

?weekday(

intellisense shows the arguments which are required or optional []. It also show that vbSunday is the default. So if you use the weekday function without specifying the start day, vbSunday is assumed.

?weekday(now)
returns 6 on a Friday.

?weekday(now, vbMonday)
returns 5 on a Friday.

Enjoy!