Weeks to go Formula

Apologies if this exists somewhere - I tried to look but couldn't find exactly what I needed.

Using smartsheet to project manage for an event - I am wanting to have a 'Weeks to Go' Column that counts down how many weeks until the exact event date along each task item.

Thanks!

Find more posts tagged with

Formulas

Marketing

Conditional Formatting

Project Management

Comments

Andrée Starå

Hi Roxanne,

One way to do this is to have two columns. One that calculates the days left and one that shows how many weeks that is. You could use TODAY or NETDAYS (see example below).

Date Column / Weeks to go column

=TODAY() - Date10 =A10 / 7

=NETDAYS(Date10; TODAY()) =A10 / 7

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant / Get Done

RoxanneF

Hey Andree - I'm still partially new to smartsheet. I input with the date but getting an error message - have I formatted this incorrectly with today's date?

=TODAY(31/01/2018) - Date10 =A10 / 7

=NETDAYS(Date10; TODAY((31/01/2018)) =A10 / 7

Andrée Starå

Hi again Roxanne,

Everything looks fine except that you don't need to put the the date in after TODAY(. It should only be TODAY() and the formula will default to todays date.

Best,

Andrée

RoxanneF

Thanks Andrew - I took out the date and just pasted the formula exactly as you wrote it but I'm getting an error message: #UNPARSEABLE

Do I need to be putting the event date somewhere so it calculates from that?

RoxanneF

Sorry - meant Andree (autocorrect!!)

Andrée Starå

Hi again Roxanne,

=TODAY() - Date1

=Diff1 / 7

=NETDAYS(Date2; TODAY())

=Diff2 / 7

Please see attached link/screenshot for a more detailed explanation.

https://cl.ly/pD1Y

I hope this helps you!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…