Date formula

Options

Hi I am trying to work on a 8 week sprint and would like to show what is in say week 1, week 2 week 3

What would be the best formal to achieve this

Thanks in advance.

Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    Options

    Hi @Cathy Fraser

    Can you provide more information? Where would the information be coming from? You could likely do a combination of the INDEX and MATCH formulas to achieve this but I'd need more information on what you're trying to achieve and where the information would be coming from.

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    Options

    I was thinking about having a sheet with the sprint weeks in them and then from the main sheet it would refernace that sheet.

  • Aya
    Aya ✭✭✭
    edited 11/08/21
    Options

    Hi Cathy,


    Instead of having a reference sheet, you can add another column for Week# with the formula:

    =WEEKNUMBER(Date@row) (reference your date column)


    Then on your Sprint Week Column you can have this formula:

    =IF([Week#]@row = 42, "Week 1", IF([Week#]@row = 43, "Week 2", IF([Week#]@row = 44, "Week 3", IF([Week#]@row = 45, "Week 4", IF([Week#]@row = 46, "Week 5", IF([Week#]@row = 47, "Week 6", IF([Week#]@row = 48, "Week 7", IF([Week#]@row = 49, "Week 8"))))))))


    Note: I based the week# assignment on your table.


    Hope this helps. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!