Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula for Days overdue

03/30/16 Edited 12/09/19

I am using smartsheets to keep track of assigned items to my team.  I want to insert a column that let's me know the days overdue.  I don't know how to insert a forumla that does this.  Can anyone help?

Popular Tags:

Comments

  • I think the formula you are looking for is:

     

    =Due Date - Today()

     

    Let me know if that helps!

  • The formula I want to suggest is: =NETDAYS([Start Date], TODAY())

     

    This forumla simply calculates the number of days between two days,and it always produces a positive number.

     

    In your sitation, I think it might be preferrable to count the number of working days (i.e. exclude weekend days and holidays according to your sheet's project setting). In this case, I suggest you try this formula: =NETWORKDAYS([Start Date], TODAY()).

     

    Another date formula, NETWORKDAY (without "S") is the same as NETWORKDAYS, but adds 1 to the calculated value if "Start Date" is a non-working day.

     

    There are various options on how to perform date-related calculations.  Please review the Date Formulas (http://help.smartsheet.com/customer/portal/articles/775363#date) and see which one fits your situation.

     

    Moreover, if there's an "End Date" column, the calculation of days overdue may only necessary when "End Date" is not set. This situation adds complexity for it is necessary to add conditional check on "Is End Date cell blank?" in the formula.

     

    Here is a a sample sheet I have come up with. Hope this helps: https://app.smartsheet.com/b/publish?EQBCT=c88a3f5da0ce41b3af7c3e9096269ce5

     

     

     

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    James,

     

    You may also want to wrap Jenny's great suggestions with a check on whether the task is overdue or done.

     

    =IF([Completed?]23,"", NETWORKDAYS([Start Date], TODAY()))

     

    This will look at the "Completed?" checkbox column, row 23, to see if the checkbox is checked. If it is, display nothing (the "") else do the formula from Jenny.

    Depending on what you have for the status will determine the exact formula, but you should get the idea.

     

    Craig

  • Hi James Melendez, be aware that using a formula that gives a new result every new day, will effect 'Modified by' field.

     

    The first person who opens the sheets in a new day, will automaltically 'change' all the rows/cells with that formula, according to the system field "Modified by' (same for Modified (date) Frown.)

     

    A glitch in Smartsheet..

  • When I try the following formula I get #UNPARSEABLE.

     

    =WORKDAY([SLA148], TODAY())

     

    SLA is the column I'm trying to calculate off of and the formula is in a different column that has Date properties.

     

    How do I correct this?

  • How do I use the Networkdays (M-F) if I have not completed the task but it is overdue? For example:

     

    Planned Completion: 09/29/17

    Actual Complete: Not completed yet

    Various: Is 119, however that includes weekends. I would like just week days.

     

    The formula I am using to get Weekdays only:

    Planned Completion - NETWORKDAY()

    I receive # INCORRECT Argument Set

This discussion has been closed.