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)

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • This worked perfectly! Thanks Genevieve!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful, I'm glad I could help!