Weeks/Days Remaining Formula

Brad Zdenek

How can I create a column that displays "x Weeks and x Days remaining" until a task's start date? I know about RYG Balls and what I can do with conditional formatting, but those just aren't sufficient for my needs.

 

In Excel/Sheets I use the MOD function to do this using the following formula:

 

=IF(([Start Date]-TODAY())>0,(INT(([Start Date]-TODAY())/7)&" weeks"&IF(MOD([Start Date]-TODAY(),7)=0,"",", "&MOD([Start Date]-TODAY(),7)&" days")),"")

 

but MOD does not seem to be supported in SmartSheet.

 

Any ideas?

John Sauber

We use a version of the following formula to display the number of weeks and days to a task's start date (perhaps obviously, our column name is [Start On]) is:

 

=INT(([Start On]1 - TODAY()) / 7) + "w, " + (([Start On]1 - TODAY()) - (7 * (INT(([Start On]1 - TODAY()) / 7))) + "d")

 

You'll have to handle things which are overdue, or if you want to consider business weeks vs. calendar weeks, but you get the idea, I'm sure. Also, if you save the sheet, close it, and do not open it until it is a new day, you'll immediately have an available "save" button, because it's a new day (today() in the formula gets re-evaluated and changes the result of the formula). Unless you save it, this is always going to happen. It's not a big deal, but you might wonder why every morning you open the sheet up, and then close it without making changes, you're prompted to save your changes.

Brad Zdenek

That is perfect. Thank you very much.

Ebony Griffiths

John, have you calculated the weeks remaining if the date in the future pans over calendar years?