Populate next Monday

@SPark

HI! My question is similar to this one: https://community.smartsheet.com/discussion/comment/81401#Comment_81401

I have two date columns (let's call them Date1 and Date2). If the date in the Date1 column is a Tuesday-Sunday I want the date in Date2 to populate with the next Monday.

e.g., if Date1 = Tuesday Feb 18 then Date2= Monday Feb 24-- but if Date1 = Monday Feb 17 then Date2= Monday Feb 17.

Hopefully that makes sense. I tried breaking down the formula in the link above but I cannot figure out how to make it the next Monday.

I am able to do this:

=IF(ISDATE(Date1@row), IF(WEEKDAY(Date1@row) = 2, Date1@row + 0, Date1@row - WEEKDAY(Date1@row) + 9))

and it works all the way until I hit a Sunday-- then it adds an extra 8 days (so populates the next, next Monday).

Paul Newcome

Take a look at THIS thread. It has a formula for populating the current Monday based on the day of the week in the Date column. You could adapt this by simply adding 7 to each of the outputs.

Paul Newcome

Take a look at THIS thread. It has a formula for populating the current Monday based on the day of the week in the Date column. You could adapt this by simply adding 7 to each of the outputs.

@SPark

@Paul Newcome you always come through for me!

Here is the final formula for anyone else:

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

Paul Newcome

Happy to help! 👍️