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

PL123

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

Jeff Reisman

@PL123

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))

Jeff Reisman

@PL123

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))

PL123

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