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

Rfitz

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

Paul Newcome

Can you post the IF statement that outputs the duration? It sounds like maybe you have quotes around the numbers which outputs a text value. If that is the case then removing the quotes form around the output numbers should clear that up for you.

Paul Newcome

Can you post the IF statement that outputs the duration? It sounds like maybe you have quotes around the numbers which outputs a text value. If that is the case then removing the quotes form around the output numbers should clear that up for you.

Rfitz

@Paul yes, you are correct- this is the formula I had:

=IF(CONTAINS("Llama", [Project Scope]@row), "60", IF(CONTAINS("cat", [Project Scope]@row), "30", IF(CONTAINS("honeybee", [Project Scope]@row), "10", IF(CONTAINS("Hamster", [Project Scope]@row), "21", IF(CONTAINS("elephant", [Project Scope]@row), "180", "0")))))

I removed the quotes from the true statement and the start NLT date calculation now works! Thank you!!

Paul Newcome

Happy to help. 👍️