Need help creating a custom duration formula

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!

Find more posts tagged with

Formulas

Accepted answers

Cody Holmes

Hi @Brooks,

Here's how I would go about the process:

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."
Go up to the toolbar and navigate to Automation -> Record a Date...
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.
Make a new column named something like "Duration."
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

All comments

Cody Holmes

Hi @Brooks,

Here's how I would go about the process:

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."
Go up to the toolbar and navigate to Automation -> Record a Date...
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.
Make a new column named something like "Duration."
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))

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

👍😎👍 -Cody

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.