Populate next Monday

@SPark ✭✭✭
edited 02/26/20 in Formulas and Functions

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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!