Date Formula ? Conditional on Day of Week

[sfleck]

I have the following that determines the number of days from a specific date.

I would like to modify so that if the result lands on a Friday, Saturday, or
Sunday that it return the first Thursday before the result

Here is the exsisting Fomula ( Developed in Office 2k3)

DateAdd("d",-DestinationShipper!DaysNeeded,MailFairTable!FairDate)

 

[Michel Walsh]

Is it is a Friday, you subtract 1, two if it is a Saturday; 3 if a Sunday,
and nothing for other days:



Choose( DatePart("w", yourDate) , -3, 0, 0, 0, 0, -1, -2) + yourDate


since DatePart("w" , someDate) returns a value for 1 to 7 dependant if the
date is a Sunday to a Saturday, so we use Choose (see help file) to
'choose' the right offset to be applied to the supplied data, 'yourDate'.



Hoping it may help,
Vanderghast, Access MVP

 

[sfleck]

Thank you I had just found a reference to the day values

It worked perfectly

ShipFix: Choose(DatePart("w",[Pref Ship]),-3,0,0,0,0,-1,-2)+[Pref Ship]

 

[Klatuu]

Here is another way to do it:
iif(weekday(dateadd("d", -6,thedate),vbmonday)<=4,dateadd("d", -6,thedate),
dateadd("d", -6 - weekday(dateadd("d", -6,thedate),vbMonday) mod 4,thedate))
--
Dave Hargis, Microsoft Access MVP

Thank you I had just found a reference to the day values

It worked perfectly

ShipFix: Choose(DatePart("w",[Pref Ship]),-3,0,0,0,0,-1,-2)+[Pref Ship]

Is it is a Friday, you subtract 1, two if it is a Saturday; 3 if a Sunday,
and nothing for other days:



Choose( DatePart("w", yourDate) , -3, 0, 0, 0, 0, -1, -2) + yourDate


since DatePart("w" , someDate) returns a value for 1 to 7 dependant if the
date is a Sunday to a Saturday, so we use Choose (see help file) to
'choose' the right offset to be applied to the supplied data, 'yourDate'.



Hoping it may help,
Vanderghast, Access MVP