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

  • Mike Y
    Mike Y ✭✭

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

  • Yaya
    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
    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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Yaya
    Yaya ✭✭
    edited 04/06/23

    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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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".

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!