Need help creating a custom duration formula

Brooks
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!

Tags:

Best Answer

  • Cody Holmes
    Cody Holmes āœ­āœ­āœ­āœ­
    Answer āœ“

    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

Answers

  • Cody Holmes
    Cody Holmes āœ­āœ­āœ­āœ­
    Answer āœ“

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!