Formula to calculate the Monday after a date

sordonez

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!

J. Craig Williams

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

 

sordonez

Thank you! That worked like a charm.

 

J. Craig Williams

That's good to hear.

Craig

Svetlana Smirnova

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

Andrée Starå

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

carl b

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!

J. Craig Williams

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

carl b

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.

J. Craig Williams

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

carl b

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!

J. Craig Williams

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

Ross Novotny

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?

CycleBagEd

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