NETWORKDAY error using cross-sheet reference

Kathy PPT
Kathy PPT ✭✭✭
edited 04/05/24 in Formulas and Functions

I would appreciate help figuring out this error please.

I'm trying to determine the number of working days from the start of the project to when it went live (Hypercare). The calculation is in the Metadata and references the Enable Service date field in the project plan. It returns #UNPARSABLE error. # days to Hypercare is a text/numeric field

Since it's the Metadata, there is only 1 row so it's tied directly the Enable Services date field in the project plan. When I view the reference, it's going to the correct field.

Thanks in advance!

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kathy PPT

    You are missing the @row as part of the [Start Date]

    it should read [Start Date]@row

    Kelly

  • Kathy PPT
    Kathy PPT ✭✭✭
    Answer ✓

    Thanks @Kelly Moore, that worked. I didn't think I needed it since there is only 1 row in the metadata sheet.

  • Kathy PPT
    Kathy PPT ✭✭✭
    edited 03/25/24

    @Kelly Moore , hoping you could answer another question.

    Using that same formula, how do I make it conditional so it only shows if the "% Complete field" on the project plan sheet is greater than 0? First pic is the one that works.



    This attempt is to use the condition. It gives #INVALID REFERENCE. Using =IF didn't work either.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kathy PPT

    To answer the @row question above, you always have to designate a row position for smartsheet to find the data. All sheets work using the coordinates of rows and columns, regardless of how many rows are in use.

    The syntax for your formula throwing the error (above) is not what smartsheet expects for NETWORKDAY. If you're not already using it, I encourage you to keep an eye on the formula helper or wizard - I don't know what's it called, that shows you what smartsheet is expecting AND the highlighted yellow portion shows you where smartsheet thinks you are in the formula. I use this frequently on complex formulas when I'm double-checking parentheses and commas (my mortal enemies in formulas 😉)


    If I understand correctly, you only want the formula to run when the % complete is greater than zero. An IF statement should have worked for this. To rule out empty cells perhaps generating a false result, we'll account for that

    =IF(AND(ISNUMBER([% Complete]@row),[% Complete]@row>0),NETWORKDAY([Start Date]@row, {Darigold Project Plan Range 1}))

    Will this work for you?

    Kelly

  • Kathy PPT
    Kathy PPT ✭✭✭

    Thanks Kelly, that works!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!