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
-
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.
-
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".
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!