Calculating # of past or upcoming days from target date

Gabe Tarin

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?

Jeff Reisman

=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()))

Bassam Khalil

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:

Gabe Tarin

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?

Jeff Reisman

=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()))

Gabe Tarin

Thank you @Jeff Reisman !

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315102#Comment_315102

My pleasure, but it was @Bassam Khalil who did the heavy lifting here!