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

SmartSheet Newbie

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

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

Frank S.

@SmartSheet Newbie

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.

Please let me know if you have any additional questions.

Frank S.

Hello @SmartSheet Newbie,

Please give this a try:

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

Let me know if you have any other questions.

SmartSheet Newbie

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

SmartSheet Newbie

Other columns that are available are

Paul Newcome

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.

SmartSheet Newbie

@Frank S.

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

SmartSheet Newbie

@Paul Newcome

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

Returns all workdays even if the status is Closed or Canceled

Paul Newcome

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

SmartSheet Newbie

@Paul Newcome

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.

Frank S.

@SmartSheet Newbie

Please confirm what you are asking to calculate:

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.

SmartSheet Newbie

@Frank S.

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.

Paul Newcome

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

Try this:

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

Frank S.

@SmartSheet Newbie

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.

Please let me know if you have any additional questions.

SmartSheet Newbie

@Frank S. @Paul Newcome

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

Paul Newcome

Happy to help. 👍️