Unique calendar year- Countback week number to delivery

Options

Hi All,


I am struggling with a formula that I think needs a simple fix, and I am hopeful the community can help.


Our product launches mid-week and runs off of a unique 35-week calendar, and we are using the following formulas to track the difference between:

THe difference between current workweek to the week of launch: =INT(([Product Announce Date]# - TODAY()) / 7)

The difference between the task delivery date to the week of launch =INT(([Product Announce Date]# - [Anticipated Task Delivery Date]@row) / 7)


Because the launch is mid-week, and while I appreciate the formula's preciseness, the numbers flip midweek.

How can I adjust the formula to still count toward the weeks to launch but support a Sunday - Sunday week?


Any help is greatly appreciated.

Tags:

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Does using WEEKNUMBER get you what you're looking for?

    https://help.smartsheet.com/function/weeknumber

    =WEEKNUMBER([Product Announce Date]#) - WEEKNUMBER(TODAY())

    =WEEKNUMBER([Product Announce Date]#) - WEEKNUMBER([Anticipated Task Delivery Date]@row)

    If this works you'll want to modify the formulas to avoid errors at the end of the year when week numbers flip from 52 (or 53) to 1.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • ABice Norton
    Options

    Hi Mark,


    Thanks for the reply. I have tried messing around with the week number formula, but since we don't work off of a traditional calendar year, the formula doesn't return what I am hoping to attain.


    The formulas I am currently using sorta works, but I am hoping to adjust them to be more precise. I think this needs to be in the TODAY piece of the formula

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    If your launch is always on a Wed try this:

    The difference between current workweek to the week of launch:

     =IF(([Product Announce Date]# - 3)- TODAY()<7, 1, INT((([Product Announce Date]#-3) - TODAY()) / 7)

    The difference between the task delivery date to the week of launch 

    =INT((([Product Announce Date]# - 3)-[Anticipated Task Delivery Date]@row) / 7)

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!