Unique calendar year- Countback week number to delivery

ABice Norton

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.

Mark Cronk

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

ABice Norton

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

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