# Formula for the week after next

Options
✭✭

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.

Thanks

Yaya

• ✭✭
Options

I think you may need to focus your question with an example - otherwise is this not simply date +14?

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 04/06/23
Options

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")

• ✭✭✭✭✭✭
Options

Double check that the column the formula is going into is set as a date type column.

• ✭✭✭✭✭✭
Options

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".

• ✭✭
Options

Thanks Paul. I am a newbie to formula. Could you please help to construct a formula for

1. activities starting in next week
2. activities starting in the week after next
3. This is looking at the "Start" date only.

Yaya

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!