Formula for the week after next

Yaya

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

Mike Y

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

Yaya

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!

Paul Newcome

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

Yaya

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

Paul Newcome

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

Paul Newcome

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

Yaya

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

Paul Newcome

You would use something along these lines:

=IF(AND([Start Date]@row>= start_range_formula, [Start Date]@row<= end_range_formula), "desired text output")