Formula to calculate the amount of time between two dates

edited 12/09/19 in Formulas and Functions

I'm trying to use the below formula to calculate the amount of days between when a project is due, and when it was actually finished.  Sometimes this will be earlier than the due date, sometimes after.

=NETWORKDAYS([Delivery Date]98, [Power Approval]98)

When the dates are the same, I'd expect the result of zero.  But it returns one.

When the project is finished a day early, I'd expect a result of -1.  But it returns -2.

When the project is finished a day late, I'd expect a result of 1.  But it returns a 2.

What can I add to this formula to get this right?  Or is there a better one out there to try?



  • Hello,

    Smartsheet treats a single day task as one working day—this concept extends to functions. NETWORKDAYS and other working day functions are designed to count the number of working days between two dates, not necessarily take the difference between two dates.

    If you're looking for a different result, you might consider adding a +1 to your function:

    =NETWORKDAYS([Delivery Date]98, [Power Approval]98) +1

    Otherwise, you can consider taking the difference between the two dates:

    =[Delivery Date]98-[Power Approval]98

    This option might result in decimal values (not an exact 1 or 0), as one of your date columns will be treated as the end of the working day, and the other as the start of the working day.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!