Date of last Wednesday of any given month in any given year

[coldstart]

Federal reporting requires me to be able to calculate the actual date value of the last Wednesday of JAN, APR, JUL and OCT in any given year. I need the date value to allow me to select reporting data using that specific date. For example: I need to select all records that were in a table on or before the last Wednesday of January in the current year. So, I need a way to compute that the last Wednesday of January was, in fact, 01/25/2012.



The solution may be simple, but it is eluding me. Can anyone steer me in the right direction? I would be MOST grateful.

[JoeM]

I'll show you how to do this in a two-step process, though you can combine both formulas into one, if you like.

Let's say that you have some date field named "MyDate".
First, let's get the last day of THAT month like this:

LastDay: DateSerial(Year([MyDate]),Month([MyDate])+1,0)

Now, based on that calculation, let's get the last Wednesday of the month using the result of the calculated field above like this:

LastWednesday: IIf(Weekday([LastDay])>=4,[LastDay]-(Weekday([LastDay])-4),[LastDay]-(Weekday([LastDay])+3))

[coldstart]

That works, Joe! I'm still having trouble following the logic of it, but it works and I've lived long enough to learn one doesn't look a gift horse in the mouth.
Thank you very much for enlightening me.

[JoeM]

Here are some "hints" that may help you out:

LastDay calculation: If you build a date and use "0" for the day part, it will return the last day of the previous month. So building October 0, 2012 will actually return September 30, 2012.

LastWednesday calculation: The WEEKDAY function pulls the weekday that a date falls on. Wednesday is represented by a "4". So, we just want to find the difference between the day the last day of the month falls on and Wednesday. If the last day is a Friday, that Weekday function would return a "6". If we subtract 4 from 6, we get two. So if we subtract 2 from our end of month calculation, we arrive at that Wednesday!

If WEEKDAY returns a number less than 4, then we need to look back at the previous week, so we need to add 7 days (so instead of having a +4, we have a -3 (4-7=3).

Let me know if you still need any more clarification on the logic.

[coldstart]

Thanks again, Joe. That does help.