How do force a date to land on the following Monday?

✭✭

I have a column with submittal date and this information will be reviewed the following Monday, then will be up for approval the Wednesday after that. Is there a way to create a review date and approval date?

I submit date on 5/17/22, I want a calculated date for the following Monday 5/23/22 then another for Wednesday 5/25/22

• ✭✭✭✭✭✭

I didn't write this formula, but I adapted it to work without helper columns. This gives you the following Monday of any date.

=IF(WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) = 2, (DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7), (DATE(YEAR([Submittal Datee]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - (WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - 2))

This one gives you the Wednesday of the week following any date.

=IF(WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) = 4, (DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7), (DATE(YEAR([Submittal Datee]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - (WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - 4))

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

I didn't write this formula, but I adapted it to work without helper columns. This gives you the following Monday of any date.

=IF(WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) = 2, (DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7), (DATE(YEAR([Submittal Datee]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - (WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - 2))

This one gives you the Wednesday of the week following any date.

=IF(WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) = 4, (DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7), (DATE(YEAR([Submittal Datee]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - (WEEKDAY(DATE(YEAR([Submittal Date]@row), 1, 1) + (WEEKNUMBER([Submittal Date]@row) + 1) * 7) - 4))

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭

@Jeff Reisman that is great! Thank you for the help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!