Xth weekday of next month formula

hzagaria

Hi,

I am trying to write a formula that will return a date that is the Xth weekday of the month following a date entry.

For example. I have 4/21/23 as my date entry. In a separate cell I want it to show the date for the second Monday of the month of May (5/8/23).

I found this formula but it is based off the current month of the Date I have already:

=IF(WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1)) <= 6, 10, 17) + (DateColumn1 - DAY(DateColumn1)) - WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1))

Any ideas on how I can adjust my formula?

Paul Newcome

REplace each instance of

DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1)

with

IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1))

hzagaria

This is my new formula but it isn't quite right still. It returns 4/8/23. The second Monday of May is the 8th, but the month that is returned is incorrect. Any suggested edits:

=IF(WEEKDAY(IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1))) <= 6, 10, 17) + (DateColumn1 - DAY(DateColumn1)) - WEEKDAY(IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1)))

Joe Goetschel

Will it always be the 2nd Monday of the next month? Or is it always going to be the next Monday in 2 weeks?

hzagaria

It is more helpful if it is the 2nd Monday of the next month. Ultimately I want to adapt the formula so that I can make it the 4th Tuesday of the next month, or the 3rd Wednesday of the next month, or the 1st Thursday of the next month.