Sign in to join the conversation:
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()
I am working on a formula to pull the row data from column "A", based on the criteria for row "B" and "C". The criteria is to look for the highest value in Column "B" and the same row "is not blank" in Column "C". In short index the row in column A, with the highest value in column B, that is also not blank in column C. I…
I am trying to write a formula that says the if the end date is before today that it selects the "Late" option out of the drop down in Status. I got most of the way there but keep getting an error. HELP
I need some help with the following formula: =WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, 0) The formula stopped working this morning, and is now showing #INVALID VALUE instead of the date (I'm expecting 12/31/2025). Can someone tell me what is wrong with the formula that it's not giving me a date?
