Calculating # of past or upcoming days from target date

Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options
    =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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!