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

mphillips

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)

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

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

mphillips

This worked perfectly! Thanks Genevieve!

Genevieve P.

Wonderful, I'm glad I could help!