Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
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()
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
