Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Weeks/Days Remaining Formula
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?
Comments
-
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.
-
That is perfect. Thank you very much.
-
John, have you calculated the weeks remaining if the date in the future pans over calendar years?
