# I need to calculate the number of working days a task has been assigned, if not closed or canceled

Below is what I have tried but it does not work.

=IF(Status@row = "Closed", ()), IF(Status@row = "Canceled", ()),NETWORKDAYS([Date Assigned]@row, TODAY())

• ✭✭✭✭✭✭

No problem, I think this should meet your needs:

=IFERROR(IF([Actual Completion Date]@row = "", NETWORKDAYS([Date Assigned]@row, TODAY()), IF(OR(Status@row = "Closed", Status@row = "Canceled"), NETWORKDAYS([Date Assigned]@row, [Actual Completion Date]@row), "")), "")

I added the IFERROR piece in case there are no dates in any of the fields we reference.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭
edited 11/30/22

Hello @SmartSheet Newbie,

=IF(OR(Status@row = "Closed", Status@row = "Canceled"), "", NETWORKDAYS([Date Assigned]@row, TODAY()))

Let me know if you have any other questions.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• Thank you for the response, your formula gives the total workdays from the date assigned to today, regardless of the status

• Other columns that are available are

• ✭✭✭✭✭✭

Does this work?

=IF(AND(Status@row <> "Closed", Status@row <> "Canceled"), NETWORKDAYS([Date Assigned]@row, TODAY()))

If the status is not closed and the status is not cancelled, output the networkdays.

• edited 12/01/22

I tried the below but it returns all workdays to today, instead of stopping the count once the status is changed to closed or canceled.

=IF(OR(Status@row = "Closed", Status@row = "Canceled"), "", NETWORKDAYS([Date Assigned]@row, TODAY()))

• =IF(AND(Status@row <> "Closed", Status@row <> "Canceled"), NETWORKDAYS([Date Assigned]@row, TODAY()))

Returns all workdays even if the status is Closed or Canceled

• ✭✭✭✭✭✭

Are you able to provide a screenshot of this happening in the sheet? How exactly is the Status column being populated?

• I have to be careful because of PII but the status is populated manually using a dropdown menu, with the the options Not Started, In Progress, Closed, and Canceled.

• ✭✭✭✭✭✭

The number of days between the date assigned and today if the status is not closed or canceled or the number of assigned days if the status is closed or canceled. Is this statement correct?

If the statement above is correct, please try the following:

=IF(OR(Status@row = "Closed", Status@row = "Canceled"),NETWORKDAYS([Date Assigned]@row, [Actual Completion Date]@row), NETWORKDAYS([Date Assigned]@row, TODAY()))

[Actual Completion Date] is from your column list above. You can substitute the column you need if this does not provide the correct date.

Formula explanation = If Status is Closed or Canceled, calculate the number of days between the date assigned and the actual completion date or calculate the number of days between the date assigned and today.

I hope this helps.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• Thank you so much for all the assistance.

I guess the best statement to describe what I am attempting to do is, "The number of days between the date assigned and today if the status is not closed or canceled and the number of days between the "Date Assigned" and the "Actual Completion Date" if the status is closed or canceled.

Another way of saying it would be "The number of days between the date assigned and today if the "Actual Completion Date" is blank and the number of days between the "Date Assigned" and the "Actual Completion Date" if the "Actual Completion Date" has been populated.

• ✭✭✭✭✭✭

Ah. Ok. That's making more sense now.

Try this:

=NETWORKDAYS([Date Assigned]@row, IF([Actual Completion Date]@row <> "", [Actual Completion Date]@row, TODAY()))

• ✭✭✭✭✭✭

No problem, I think this should meet your needs:

=IFERROR(IF([Actual Completion Date]@row = "", NETWORKDAYS([Date Assigned]@row, TODAY()), IF(OR(Status@row = "Closed", Status@row = "Canceled"), NETWORKDAYS([Date Assigned]@row, [Actual Completion Date]@row), "")), "")

I added the IFERROR piece in case there are no dates in any of the fields we reference.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• I think it is working now, thank you both for all the assistance.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!