Networkdays and If formula help

JMT

Hello! I could really use some help. I've reviewed previous Q&As and can't get my formula to work. I want to calculate net workdays based on a specific status selection and have it stop counting days once the status has changed. Status is in dropdown selection box.

I've automated the start date to populate when the "Waiting on Data" status is selected. What is a good formula to say, "count net workdays IF status is "Waiting on Data" and stop counting days when status is not "Waiting on Data"?

My latest formula attempt is unparseable.

=IF((Status@row, ='Waiting on Data'), NETWORKDAYS([Waiting on Data Start Date]@row, TODAY))

Thank you in advance for your help!!

JMT

JMT

Hello Paul,

Thank you very much! It worked! When I tried it as written, it returned a 1 value but when I changed the <> to =, I received the correct data.

I really appreciate your help.

Thank you,

JMT

Paul Newcome

You need to insert a date type column along with a Record A Date automation that will capture the date when that changes. From there the formula would look like this...

=NETWORKDAYS([Waiting On Data Start Date]@row, IF([Helper Column]@row <> "", [Helper Column]@row, TODAY()))

JMT

Hello Paul,

Thank you very much! It worked! When I tried it as written, it returned a 1 value but when I changed the <> to =, I received the correct data.

I really appreciate your help.

Thank you,

JMT

Paul Newcome

Happy to help. 👍️

JMT

Hi Paul,

Now that I've entered more data, I did go back to your original formula. What I notice is when a date is entered into Date Process Ends column (trigger to stop tracking net workdays), it continues to track the net workdays. In short, it should only count net workdays until the Date Process Ends is no longer empty. Is there a way to enter a false value in my Days Out (column counting days a process is open) so that it will count the days the process doesn't have an end date then stop tracking the days when an end date is entered? I'm fine if it simply reflects "Done" or "n/a" after an end date is entered.

Thank you again for your help!!!

Warm regards,

JMT

Paul Newcome

So if the process has ended, what exactly would you use for the start date? Still the [Waiting On Data Start Date]@row?

And then I assume you want to basically replace TODAY() with [Date Process Ends]@row once that date is entered?

JMT

Goal: Track the days between a request is submitted incomplete (no data) and trigger a checkbox to identify incomplete requests that are 60+ days. If 60+ days, the request will be updated or inactivated (to eliminate noise in our data). We are identifying requests that are open for more than 59 days on a dashboard to alert the team to take some action on the request.

Here are my fields:

Start date is set up as an automation workflow. If the request status is "upcoming request", the system will record a date in the start date column.

End date is also set up as an automation workflow. Once the request status changes from "upcoming' to "analysis", an end date is automatically recorded in the end date column.

Days Open: this is where I want a formula to count the number of days between the start date and Today IF the end date column is empty. Once the end date cell has a date recorded, the Days Open cell (my formula) should stop counting days and be blank or have a some reflection that it is no longer open.

">= 60 Days Open" is set up for automation workflow where if the Days Open are over 59 days, a box will be checked and unchecked once the Days Open are below 59 days or blank.

I can't help but wonder if I am making this too hard. :)

Paul Newcome

Try something like this...

=IF(AND([End Date]@row = "", NETWORKDAYS([Start Date]@row, TODAY()) < 60), NETWORKDAYS([Start Date]@row, TODAY()))

JMT

Thank you but it's blank. The cell is not calculating the open days. I tried fixing this by using the clear cell value automation. That does clear the cell but it also removes the formula...

Paul Newcome

Yes. The clear cell value automation will remove the formula. Why are you trying to clear it out? Are you trying to combine this with the first formula, or is this going into a different column?

JMT

Since my checkmark identifying the open date was ">59" days, I couldn't get rid of the checkmark as long as the open days kept counting, even after an end date was entered.

Paul Newcome

There are a couple of different ways this can be handled. I would suggest a basic IF statement to say that if the [End Date]@row is blank then run the calculation, otherwise leave blank.

JMT

You are BRILLIANT! Done! Tested and working perfectly. Thank you so much!

Paul Newcome

Happy to help. 👍️