Need help creating a custom duration formula

Brooks

Hi! I need help with a formula to automatically update the "duration" column each day as well as only count weekdays and not weekends. Is this possible?

Here is some more information.

There is a "date submitted" column.

A "status" column with three options: Not started, In progress, and Complete.

There is a "duration" column. This column is meant to record the number of days since a request was submitted. This number should update daily until the "status" column is marked "complete."

Thank you for your assistance!

Cody Holmes

Hi @Brooks,

Here's how I would go about the process:

1. Ensure that you have a column named something like "Date Completed," a placeholder column to record the date that the status was moved to "complete."
2. Go up to the toolbar and navigate to Automation -> Record a Date...
3. Set up a workflow automation based on the status column. When the status column changes to "complete," make it where the date is recorded in the "Date Completed" column.
4. Make a new column named something like "Duration."
5. In the Duration column, input a formula similar to the one below:

=IF(ISERROR(NETWORKDAYS([Start Date]@row, [Date Completed]@row)), "n/a", NETWORKDAYS([Start Date]@row, [Date Completed]@row))

.

.

Basically, this checks if the calculation is producing an error, which will be the case when there is no Completed Date value. If there is an error, I made it where it returns "n/a." You can change this as needed. But if there is no error (i.e. there is a date in the Completed Date column), the function should work as you intend.

Note: You may want to change the NETWORKDAYS function to NETWORKDAY if you want to include the count of a day if the start date falls on a weekend. Otherwise, NETWORKDAYS is probably what you are looking for. It's more commonly used.

Hope that helps! Be sure to give me a vote and accept my answer if I proved helpful.

👍😎👍 -Cody

Cody Holmes

Hi @Brooks,

Here's how I would go about the process:

1. Ensure that you have a column named something like "Date Completed," a placeholder column to record the date that the status was moved to "complete."
2. Go up to the toolbar and navigate to Automation -> Record a Date...
3. Set up a workflow automation based on the status column. When the status column changes to "complete," make it where the date is recorded in the "Date Completed" column.
4. Make a new column named something like "Duration."
5. In the Duration column, input a formula similar to the one below:

=IF(ISERROR(NETWORKDAYS([Start Date]@row, [Date Completed]@row)), "n/a", NETWORKDAYS([Start Date]@row, [Date Completed]@row))

.

.

Basically, this checks if the calculation is producing an error, which will be the case when there is no Completed Date value. If there is an error, I made it where it returns "n/a." You can change this as needed. But if there is no error (i.e. there is a date in the Completed Date column), the function should work as you intend.

Note: You may want to change the NETWORKDAYS function to NETWORKDAY if you want to include the count of a day if the start date falls on a weekend. Otherwise, NETWORKDAYS is probably what you are looking for. It's more commonly used.

Hope that helps! Be sure to give me a vote and accept my answer if I proved helpful.

👍😎👍 -Cody