Calculating # of past or upcoming days from target date
I am trying to write a formula that calculates the number of days from a target finish date. I am using the =NETDAYS function but it is only calculating the days as a positive number. How can I write the formula so that it counts down days from an upcoming target date and also displays (-)days past that target date?
Best Answer
-
=IF([Finish Date] @row > TODAY(), NETWORKDAYS(TODAY(), [Finish Date] @row), -1 * NETWORKDAYS([Finish Date] @row, TODAY()))
You have spaces between [Finish Date] and @row. It should be [Finish Date]@row, as shown here:
=IF([Finish Date]@row > TODAY(), NETWORKDAYS(TODAY(), [Finish Date]@row), -1 * NETWORKDAYS([Finish Date]@row, TODAY()))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Hi @Gabe Tarin
Hope you are fine, please try the following formula:
=IF([target finish date]@row > TODAY(), NETWORKDAYS(TODAY(), [target finish date]@row), -1 * NETWORKDAYS([target finish date]@row, TODAY()))
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thanks Bassam,
I input the formula like this: =IF([Finish Date] @row > TODAY(), NETWORKDAYS(TODAY(), [Finish Date] @row), -1 * NETWORKDAYS([Finish Date] @row, TODAY())) but I am getting a syntax error. Any ideas?
-
=IF([Finish Date] @row > TODAY(), NETWORKDAYS(TODAY(), [Finish Date] @row), -1 * NETWORKDAYS([Finish Date] @row, TODAY()))
You have spaces between [Finish Date] and @row. It should be [Finish Date]@row, as shown here:
=IF([Finish Date]@row > TODAY(), NETWORKDAYS(TODAY(), [Finish Date]@row), -1 * NETWORKDAYS([Finish Date]@row, TODAY()))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you @Jeff Reisman !
-
My pleasure, but it was @Bassam Khalil who did the heavy lifting here!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!