Xth weekday of next month formula

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?

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.