Help with date parts

[JimO]

I want a formula to display the date of the next 4th Wed. of the month, that would also roll over to next month if necessary.



Date parts confuse me.

Thanks
Jim O

[Micron]

Likely someone will figure this out before me, but I think you at least need to tell us what the input will be. One needs something to work with be that any date, or any date in any given month, or the current date...
Since today is 08/11/2019 (aug) the result you are looking for is Aug 28?

[JimO]

The current date. I can use Date(), I think.

[June7]

Look at http://www.cpearson.com/excel/DateTimeWS.htm - see the item "Nth Day Of Week For A Month And Year". Those formulas are for Excel. Adapted for Access and calculated in Immediate Window:

?Date() + 29 - Day(Date())-Weekday(Date()-Day(Date())-3)
8/28/2019

And with the first day of month:

?#8/1/2019#+28-Weekday(#8/1/2019#-4)
8/28/2019

Since if the given date is after 4th Wed, you then want next month's 4th Wed, write a VBA custom function to return appropriate date. Something like:

Function GetWed(dteDate As Date) As Date
Dim dteWed As Date
dteWed = dteDate + 29 - Day(dteDate) - Weekday(dteDate - Day(dteDate) - 3)
GetWed = dteWed
If dteDate > dteWed Then
    GetWed = DateAdd("m", 1, dteDate) + 29 - Day(DateAdd("m", 1, dteDate)) - Weekday(DateAdd("m", 1, dteDate) - Day(DateAdd("m", 1, dteDate)) - 3)
End If
End Function