Using Formula - Display Wednesday of Every Week based on Submitted Date

I have a publication that's sent out every Wednesday and allows people to submit articles to show up in the weekly publication. I need help writing a formula that would make the "Publish Date" column anything submitted between Thursday-Wednesday to show that week's Wednesday date and everything outside to show the following week and so on (see table example below) .

I have a formula that works on a week basis, but doesn't account for dates that go past Wednesday:

=[Submission Date]@row - (WEEKDAY([Submission Date]@row) - 4)

Find more posts tagged with

Sheets

Formulas

Accepted answers

Genevieve P.

Hi @mphillips

The way I would do this is to use an IF statement that looks at the Submission Date and says IF this date is Sunday, add 3 days (to make it Wednesday). IF it's Monday, add 2 days.... and so on.

Try this full formula:

=IF(WEEKDAY([Submission Date]@row) = 1, [Submission Date]@row + 3, IF(WEEKDAY([Submission Date]@row) = 2, [Submission Date]@row + 2, IF(WEEKDAY([Submission Date]@row) = 3, [Submission Date]@row + 1, IF(WEEKDAY([Submission Date]@row) = 4, [Submission Date]@row, IF(WEEKDAY([Submission Date]@row) = 5, [Submission Date]@row + 6, IF(WEEKDAY([Submission Date]@row) = 6, [Submission Date]@row + 5, IF(WEEKDAY([Submission Date]@row) = 7, [Submission Date]@row + 4)))))))

Cheers!

Genevieve

All comments

Genevieve P.

Hi @mphillips

The way I would do this is to use an IF statement that looks at the Submission Date and says IF this date is Sunday, add 3 days (to make it Wednesday). IF it's Monday, add 2 days.... and so on.

Try this full formula:

Cheers!

Genevieve

mphillips

This worked perfectly! Thanks Genevieve!

Genevieve P.

Wonderful, I'm glad I could help!

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.