Date Formula ? Conditional on Day of Week

  • Thread starter Thread starter sfleck
  • Start date Start date
S

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)
 
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
 
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]
 
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


sfleck said:
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]

Michel Walsh said:
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
Click to expand...
Click to expand...
 
Back
Top