Count Down the Remaining Working Days

Good Day Smartsheet Community:

I am Trying to Modify my Existing Formula to Count Down the Remaining Days. Right Now it is Static, but I think if I Subtract Today it Should Start Counting Down, but I not sure where to Insert Today in my Formula.

=NETWORKDAYS([Q3 Start]@row, [Q3 End]@row)

Results: 64

Thanks

Find more posts tagged with

Accepted answers

Brent Wilson

The Today() function would start your countdown using the end date

=NETWORKDAYS(Today(), [Q3 End]@row)

You might want something like this which uses you old formula until Q3 Start = Today

=IF( [Q3 Start]@row <= Today(), NETWORKDAYS(Today(), [Q3 End]@row), NETWORKDAYS([Q3 Start]@row, [Q3 End]@row))

This would start reducing the number of days you have after reaching the date stored in Q3 Start

So it would sit.. 64.. 64.. 64.. 64.. 64.. 64.. 63.. 62.. and so on.. It will also go negative if the End Date passes

All comments

Jeff Reisman

@Kal

The syntax for NETWORKDAYS is NETWORKDAYS(start_date, end_date, [ holidays ])

=NETWORKDAYS(TODAY(0), [Q3 End]@row)

Keep in mind a few things:

The TODAY function only updates its value if the sheet is opened or rows are otherwise modified in some way. Because of this, you may want to add a helper column called CurrentDate, and use scheduled automation at 1am each morning to record the date in that field. Then use =NETWORKDAYS([CurrentDate]@row, [Q3 End]@row) instead of TODAY(0).

Also, with this formula in place, once we're past the [Q3 End]@row date, the value in this formula cell will start counting negative. If you don't want that, you might consider an IF statement as part of the formula:

=IF([CurrentDate]@row <= [Q3 End]@row, NETWORKDAYS([CurrentDate]@row, [Q3 End]@row), 0)

In English, if the current date is less than the Q3 End date, count the networkdays from current date to Q3 End date, otherwise, set this cell to 0 (or whatever alternate value you'd want there.)

Brent Wilson

The Today() function would start your countdown using the end date

=NETWORKDAYS(Today(), [Q3 End]@row)

You might want something like this which uses you old formula until Q3 Start = Today

=IF( [Q3 Start]@row <= Today(), NETWORKDAYS(Today(), [Q3 End]@row), NETWORKDAYS([Q3 Start]@row, [Q3 End]@row))

This would start reducing the number of days you have after reaching the date stored in Q3 Start

So it would sit.. 64.. 64.. 64.. 64.. 64.. 64.. 63.. 62.. and so on.. It will also go negative if the End Date passes

Kal

https://community.smartsheet.com/discussion/comment/335856#Comment_335856

Thanks Jeff

Kal

https://community.smartsheet.com/discussion/comment/335857#Comment_335857

Thanks Brent!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.