Formula to calculate the Monday after a date
I'm looking for a way to calculate the Monday after an event date, to trigger the next step in my project plan. Any ideas? Thanks in advance!
Comments

Project Plan? Are you using Dependencies?
This formula returns the "NEXT Monday":
=IF(WEEKDAY(Date@row) = 1, Date@row  (WEEKDAY(Date@row)  2), Date@row  (WEEKDAY(Date@row)  2) + 7)
including today (it will return 20180827 for today)
Craig

Thank you! That worked like a charm.

That's good to hear.
Craig

Helli? please, can u help me, i need formula "NEXT thursday" in a week from the date . for example for dates 20190408, 20190409, 20190410 i need to get 20190418.. thank you

Hi Svetlana,
Try this.
=IF(WEEKDAY(Date@row) = 1; Date@row  (WEEKDAY(Date@row)  5); Date @row  (WEEKDAY(Date @row)  5) + 7)
The same version but with the below changes for your and others convenience.
=IF(WEEKDAY(Date@row) = 1, Date@row  (WEEKDAY(Date@row)  5), Date@row  (WEEKDAY(Date@row)  5) + 7)
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Hi J Craig,
Sorry to resurrect this thread a year later, but this was the only thread somewhat pertinent to the issue I'm having with my smartsheet. I am attempting to calculate something similar that would say if the event fell on any day before Wednesday, the following step would be the following Monday; however, if the event fell on a Thursday or any day in the week thereafter, the following step would come out to two Mondays after.
I originally had a formula in my Excel copy but Smartsheet doesn't seem to like it and is giving me an error saying incorrect argument set. We have:
=IF(WEEKDAY([Example Date]6) > 4, [Example Date]6 + (14  WEEKDAY([Example Date]6, 2) + 1), [Example Date]6 + (7  WEEKDAY([Example Date]6, 2) + 1))
Are you able to see what I am doing wrong that is producing this error? Thanks so much for your assistance! 
Carl
Try this
=IF(WEEKDAY(Date@row) > 4, Date@row  (WEEKDAY(Date@row)  2) + 14, Date@row  (WEEKDAY(Date@row)  2) + 7)
If date is SunWed, formula returns the following week's Monday
If date is ThurSat, formula returns the Monday after that (second Monday)
I hope that helps.
Craig

Hi J Craig,
Thanks so much, that helps a lot! One more question, what should I do if I want to calculate the last Monday of the month that follows Date@row? For example, if Date@row falls on anytime in September, then the formula would calculate out to the last Monday of October. Smartsheet doesn't use EOMONTH like Excel does, apparently.

That is a bit trickier, but here it is, in two parts
(1) This formula will return the last day of the month of NEXT month.
=IF(MONTH(Date@row) > 10, DATE(YEAR(Date@row) + 1, MONTH(Date@row)  10, 1)  1, DATE(YEAR(Date@row), MONTH(Date@row) + 2, 1)  1)
(2) This formula will take the results of (1) and return the previous Monday (the last Monday of the month)
=IF(WEEKDAY(EOM@row) = 1, EOM@row  6, EOM@row  WEEKDAY(EOM@row) + 2)
You can combine these into one formula by replacing all "EOM@row" in (2) with the formula in (1).
Because there are 4 of them, I would either
a) not do that
b) create the EOM column and replace the formula for each EOM@row one at a time to ensure nothing gets broken.
Craig

Hi Craig, I was able to create the formula by combining both parts into one and got the result I needed. Thanks so much! Now one last question (I promise), if I am given a date, what should I do to calculate the first date of the following month? Essentially, it needs to default to the first of the next month following the given date, so if the date I have is February 24th then my result needs to be March 1st.
Thanks again for the assistance!

Here is one way
=IF(MONTH(Date@row) = 12, DATE(YEAR(Date@row) + 1, 1, 1), DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1))
Craig

In addition to this information, I am looking to find the previous Monday for any date MondaySunday of the following week. So if the previous Monday was 10/5/20, it would show 10/5/20 in a cell until it reached 10/12/20. The formula: =IF(ISDATE(Date@row), IF(WEEKDAY(Date@row) = 7, Date@row, Date@row  WEEKDAY(Date@row)  5)) works except for Saturdays. What adjustments do I need to make here to acommodate for the weekends?

Try
=EVENT_DATE  MOD(EVENT_DATE  (DATE(1900, 1, 1)), 7) for the Monday before. Add 7 for the Monday after.
Also works with TODAY()
Help Article Resources
Categories
Check out the Formula Handbook template!