How can I calculate a start date using a due date (end date) and a calculated duration in days?

Rfitz ✭✭
edited 08/23/22 in Formulas and Functions

Here are the relevant columns I have:

Project Scope: we use a categorizing system with 5 different categories. Each category corresponds to a number of days. For example, "honeybee" takes 10 days, "cat" takes 30 days.

Project Duration: I used an IF(CONTAINS) formula to calculate a number of working days needed based on the project scope. So, if the "Project Scope" column says "honeybee" the "Project Duration" Column will say "10". Each row in my sheet will vary in duration based on the assigned scope.

Due date: This is the date the project is due. It is a date field.

What I want: A "Start no later than" date column that calculates the necessary project start date using the due date and subtracting the number of days listed in the project duration column using only workdays.

here's an example: Project duration column says "10", due date says "7/29/22. I want to create a formula in the "start no later than" column that will give me the necessary start date. So in this example, it should say "7/15/22"

I have tried multiple formulas and am not getting anywhere close.

I am getting an #invalid operation response from this formula:

=[Due Date]@row - [Project Duration]@row

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!