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!
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
Thank you! That worked like a charm.
That's good to hear.
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
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
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 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.
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.
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))
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?
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()
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
I have tried to get smartsheet to auto check a box 24 hours after a row is created and column "Task Started" is not checked. =IF(AND([Task Started]@row <> 1, TODAY() - Created@row >= 1), 1) This returns #UNPARSEABLE. Any help would be appreciated.
