WEEKNUMBER formula

Options

Hello, I am trying to count the number of weeks between two dates. I have applied this formula

=WEEKNUMBER([SD End]@row - WEEKNUMBER([SD Start]@row))

It doesn't seem to be calculating correctly. For example, returning "51" for a SD End date 2/3/23 - SD Start 11/14/22. That answer should be more like 11, right? What am I missing?

Tags:

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    Hi, @holli.nunn, modify your formula to...

    =(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)

    You needed to account for start-end dates that span across a calendar year so the expression,

    YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 ,gives you the year(s) measured in weeks.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @holli.nunn

    I hope you're well and safe!

    =(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)

    Try copying/pasting the formula.

    Did that work?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!