Formula to calculate the Monday after a date

Options
edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭
Options

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 2018-08-27 for today)

Craig

• Options

Thank you! That worked like a charm.

• ✭✭✭✭✭✭
Options

That's good to hear.

Craig

• Options

Helli? please, can u help me,  i need formula "NEXT thursday" in a week from the date . for example for dates 2019-04-08, 2019-04-09,  2019-04-10 i need to get 2019-04-18.. thank you

• ✭✭✭✭✭✭
Options

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 semi-colon 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.

• Options

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!

• ✭✭✭✭✭✭
Options

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 Sun-Wed, formula returns the following week's Monday

If date is Thur-Sat, formula returns the Monday after that (second Monday)

I hope that helps.

Craig

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

In addition to this information, I am looking to find the previous Monday for any date Monday-Sunday 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?

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!