Calculating ETA +3 days not to fall on weekends

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an APPEND Query which appends data to a list of parts with expected
due dates.
I have a calculation that gives all the parts ETA's of three days.

This is fine for Date()+3

However what if the weekeday is Thursday or Friday. I wish it to calculate
to the Monday or Tuesday.

So far I have worked out this:

IIf(Weekday(Date()+3)=1,Date()+4,Date()+3 Or
IIf(Weekday(Date()+3)=7,Date()+5,Date()+3))

This returns a date 29/12/1899 (UK Dates 29th Dec 1899)
Which would be ok for my great great grandfather, however if I take part of
the equation up to the "Or" comparison it works out fine. In fact this
corresponds that if the date calculated ends up on Saturday then add four
days.

IIf(Weekday(Date()+3)=1,Date()+4,Date()+3

Is there a better formula for my calculation.

Thanks in advance for your help.

Andi Lee Davis
 
IIf(DatePart("w",Date()+2)=7,Date()+4,Date()+2) Or
IIf(DatePart("w",Date()+2)=1,Date()+5,Date()+2)I have also tried this
 
Solved it!!!

A little tinkering around... I came up with this...

IIf(Weekday(Date()+2)=7,Date()+4,IIf(DatePart("w",Date()+2)=1,Date()+4,Date()+2))

and it works dandy.

Andi Lee Davo
 
Back
Top