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, pleaseVisit the Current Forums.

Formula for Days overdue

James Melendez
James Melendez ✭
edited 12/09/19 inArchived 2016 Posts

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?

Tags:
· Share on Facebook Share on Twitter

Comments

  • tobrien3
    tobrien3 ✭

    I think the formula you are looking for is:

    =Due Date - Today()

    让我知道帮助!

    · Share on Facebook Share on Twitter
  • Jenny Lam
    Jenny Lam ✭

    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.

    在你的情况,我认为它可能preferrableto 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

    · Share on Facebook Share on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    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

    · Share on Facebook Share on Twitter
  • Rob van Dijk
    Rob van Dijk ✭

    HiJames 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..

    · Share on Facebook Share on Twitter
  • aaronakzin
    aaronakzin ✭

    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?

    · Share on Facebook Share on Twitter
  • Kal-El
    Kal-El ✭

    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

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

CHILDREN will only look at entries one level down. DESCENDANTS will look at all levels below current.<\/p>"},{"commentID":383208,"body":"

Thank you Carson, very helpful!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107090,"type":"question","name":"Calculate the # of months between 2 dates, with 1 date being static","excerpt":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12\/1\/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…","snippet":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my…","categoryID":322,"dateInserted":"2023-06-29T15:55:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T18:11:28+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T18:17:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3376118209,"url":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107090,"commentID":383181,"name":"Re: Calculate the # of months between 2 dates, with 1 date being static","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383181#Comment_383181","dateInserted":"2023-06-29T18:11:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T17:24:10+00:00","dateAnswered":"2023-06-29T17:11:25+00:00","acceptedAnswers":[{"commentID":383158,"body":"

Try a DATE function:<\/p>

=(NETDAYS([Requested Start Date]@row, DATE(2022, 12, 1)<\/strong>) \/ 365) * 12<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107082,"type":"question","name":"Automatic Update of Dropdown List?","excerpt":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori","snippet":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet?…","categoryID":322,"dateInserted":"2023-06-29T14:16:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T18:02:59+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T18:08:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T19:42:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":50,"score":null,"hot":3376116552,"url":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","format":"Rich","lastPost":{"discussionID":107082,"commentID":383175,"name":"Re: Automatic Update of Dropdown List?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383175#Comment_383175","dateInserted":"2023-06-29T18:02:59+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T19:42:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T14:52:00+00:00","dateAnswered":"2023-06-29T14:50:26+00:00","acceptedAnswers":[{"commentID":383100,"body":"

@maineL<\/a>,<\/p>

I haven't seen this possible without the use of a 3rd party integration. I don't believe it is supported natively in Smartsheet. The question comes up all the time though. <\/p>

BRgds,<\/p>

-Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • HAS and Children Functions

    I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), "Text"), 1, 0) which sets the value of column1 to 1 if "Text" is found in any children in Column2 This formula works well for a single nested file of children. However, I have a multi-level nested file (i.e. Parent1, Child1, Parent2, Child2) and it…

  • Calculate the # of months between 2 dates, with 1 date being static

    Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…

  • Automatic Update of Dropdown List?

    I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori