Formula for the week after next
Hi, wondering if anyone could help me to put together a formula for the week after next week?
I'd like to show activities with the "Start", which falls in the WEEK AFTER NEXT WEEK.
Many thanks in advance!
Thanks
Yaya
Answers
-
I think you may need to focus your question with an example - otherwise is this not simply date +14?
-
Thanks. Taking today 6 of April as an example, I'd to have a automatic formula which calculates all the activities that "start" within the week, 17 - 23 of April. So even if today shifts, it always automatically calculates the week after.
Any help would be appreciated!
-
First step is to get the "current Monday". Then we add 14 to that to get the start date and 20 to get the end date.
Start Date of range:
=TODAY() + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) + 14
End Date of range:
=Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) + 20
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul
Not sure why it's returning as invalid column value?
Thanks
I have this formula to show activities for next week, which works pretty well. Any chance we could align to this?
=IF(AND(YEAR(TODAY()) = YEAR(Start@row), WEEKNUMBER(TODAY() + 1) + 1 = WEEKNUMBER(Start@row)), "NextWeek")
-
Double check that the column the formula is going into is set as a date type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It also looks like your "Next Week" formula is not going to trigger when we are in the last week of December and next week is the first week of January / a new year. I would suggest using the formula in my last post but adding 7 and 13 instead of 14 and 20.
If you are looking to output text instead of a date, you would need to nest it in an IF statement. Basically you would say that if the date is greater than or equal to the "start date" and less than or equal to the "end date" then output "text of choice".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul. I am a newbie to formula. Could you please help to construct a formula for
- activities starting in next week
- activities starting in the week after next
- This is looking at the "Start" date only.
Yaya
-
You would use something along these lines:
=IF(AND([Start Date]@row>= start_range_formula, [Start Date]@row<= end_range_formula), "desired text output")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!