I would like to count the aging but I'm getting #UNPARSEABLE in this formula.

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Debbie Sawyer , Thanks for helping!

    @Maryjo Reyes ,

    I found a paren out of place. Try this:

    =IFERROR(IF(Status@row="Closed", NETWORKDAYS([date served]@row, [date of actual close-out]@row), NETWORKDAYS([date served]@row, [date today]@row)),"")

    If it still doesn't work, confirm that all of your columns with dates are set as date columns in the properties.

    We'll help you figure this out.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Maryjo Reyes

    I can see a black parenthesis at the end of your formula - this indicates you have too many. The blue one indicates you have the correct number of them but doesn't necessarily indicate they are placed in the correct position. Delete the last black parenthesis.

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Maryjo Reyes ,

    Try:

    =IFERROR(IF(Status@row="Closed", NETWORKDAYS([date served]@row, [date of actual close-out]@row), NETWORKDAYS([date served]@row, [date today]@row),"")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Maryjo Reyes
    Maryjo Reyes ✭✭

    Hi Mark,

    Doesn't work. Is there any way in Smartsheet that it will compute the aging when the Status is Open and stop computing when the status is Closed. Thanks

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/28/21

    Hi @Maryjo Reyes ,

    Did it give you an error or the wrong answer?

    Yes, you can create a formula so the aging stops reporting when Closed. You'd use the same logic =IF(Status@row="Closed, then... logic.

    Did you try just removing the extra parne from your original formula as Kelly (@KDM ) suggested?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Maryjo Reyes
    Maryjo Reyes ✭✭

    It gives #INCORRECT. Also tried what KDM says but it gives me same results.

    · Share on FacebookShare on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Maryjo

    Have you copied and pasted Marks formula into your sheet, or did you retype it? He has changed the first networkdays function from using a minus sign to having a comma. If you still have a minus sign in that part of the formula you will receive an #incorrect arguments type error.

    Your original

    =IF(Status@row="Closed", (NETWORKDAYS([Date Served]2 - [Date of Actual Close-out]2),1), (NETWORKDAYS([Date Served]@row, [Date Today]@row)),1))))

    Mark's Suggestion

    =IFERROR(IF(Status@row="Closed", NETWORKDAYS([date served]@row, [date of actual close-out]@row), NETWORKDAYS([date served]@row, [date today]@row),"")

    Have you got Mark's formula entered exactly as it is here? If you didn't try copying and pasting from here before, give it a go, as Mark's suggestion should work for you.

    Kind regards

    Debbie

    · Share on FacebookShare on Twitter
  • Maryjo Reyes
    Maryjo Reyes ✭✭

    Hi Debbie,

    Tried it already, still gives me #INCORRECT.

    1.png


    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Debbie Sawyer , Thanks for helping!

    @Maryjo Reyes ,

    I found a paren out of place. Try this:

    =IFERROR(IF(Status@row="Closed", NETWORKDAYS([date served]@row, [date of actual close-out]@row), NETWORKDAYS([date served]@row, [date today]@row)),"")

    If it still doesn't work, confirm that all of your columns with dates are set as date columns in the properties.

    We'll help you figure this out.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Maryjo Reyes
    Maryjo Reyes ✭✭
    https://community.smartsheet.com/discussion/comment/277328#Comment_277328

    Hi Mark,

    It works! Thanks for your help.

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Perfect. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!