Count Netdays if Project Status is "Complete"

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)"

Screen Shot 2022-01-16 at 11.14.56 PM.png

Find more posts tagged with

Formulas

Reports

functionality

Project Management

Accepted answers

Dearick Milton

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:

Project Status is not "Complete";
Actual Start Date is blank;
Actual End Date is blank; or
% 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))

All comments

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.

Dearick Milton

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

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:

Project Status is not "Complete";
Actual Start Date is blank;
Actual End Date is blank; or
% 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

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))

Dearick Milton

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.