Count Netdays if Project Status is "Complete"

Dearick Milton
edited 01/17/22 in Formulas and Functions

Hello Forum,

I am having difficulty writing a formula to count NETDAYS if a project status is "Complete". I hope this is not too confusing, but here goes.....


  • 3 COLUMNS: "Project Status", "Actual Start Date", and "Actual End Date"

I have a grid with 200+ projects/rows (Project Intake Sheet), and would like the formula to look for "Complete" at each row of the "Project Status" column.


IF the "Project Status" is "Complete" (at the column/row vertex) ... then calculate the NETDAYS of the corresponding "Actual Start Date" and "Actual End Date" for each row..


NOTE: (I am creating this formula in a separate grid/sheet and am referencing the columns in the Project Intake Sheet.


I have tried this formula but it is unparsable:

=IF({Project Intake Sheet Range 1}"Complete",NETDAYS({Project Intake Sheet Range 2},{Project Intake Sheet Range 3}))


I don't know how to get the formula to do this for each row.

When selecting the "Project Status" column, it returns "(Project Intake Sheet Range 1)"

When selecting the "Actual Start Date" column, it returns "(Project Intake Sheet Range 2)"

When selecting the "Actual End Date" column, it returns "(Project Intake Sheet Range 3)"



Best Answer

  • Dearick Milton
    Answer ✓

    Hi Paul,

    I worked on this a little more and was able to figure it out. As you recommended, I did create a column on the source sheet to calculate NETDAYS.

    If either of the following criteria is true, the NETDAYS column will not be calculated:

    1. Project Status is not "Complete";
    2. Actual Start Date is blank;
    3. Actual End Date is blank; or
    4. % Complete is not equal to 100%.

    If all criteria is false, the formula will calculate the NETDAYS in the respective column.

    =IF(OR([Project Status]@row <> "Complete", [Actual Start Date]@row = "", [Actual End Date]@row = "", [% Complete]@row < 0.1), "", NETDAYS([Actual Start Date]@row, [Actual End Date]@row))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to have to create a column on the source sheet that will calculate the NETDAYS and then sum this column using cross sheet references on the second sheet.

  • Hi Paul,

    Thank you very much for the information. I really appreciate it.

    Another question, please. If I create the column to calculate NETDAYS, how do I prevent #UNPARSABLE from appearing if the Actual Start and End Dates have not been populated yet?

    I tried the following and it does work:

    =IF(OR([Actual Start Date]@row = "")), IF(OR([Actual End Date]@row = ""), =NETDAYS([Actual Start Date]@row, [Actual End Date]@row),"")

  • Dearick Milton
    Answer ✓

    Hi Paul,

    I worked on this a little more and was able to figure it out. As you recommended, I did create a column on the source sheet to calculate NETDAYS.

    If either of the following criteria is true, the NETDAYS column will not be calculated:

    1. Project Status is not "Complete";
    2. Actual Start Date is blank;
    3. Actual End Date is blank; or
    4. % Complete is not equal to 100%.

    If all criteria is false, the formula will calculate the NETDAYS in the respective column.

    =IF(OR([Project Status]@row <> "Complete", [Actual Start Date]@row = "", [Actual End Date]@row = "", [% Complete]@row < 0.1), "", NETDAYS([Actual Start Date]@row, [Actual End Date]@row))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to get it started. I did notice though that your formula is only going to calculate when the % Complete is less than 10%. 100% when referenced in a formula is 1.


    =IF(OR([Project Status]@row <> "Complete", [Actual Start Date]@row = "", [Actual End Date]@row = "", [% Complete]@row < 1), "", NETDAYS([Actual Start Date]@row, [Actual End Date]@row))

  • Hi Paul, Thank you very much for catching that error for me. Much appreciated. :-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!