Trying to back into a date and also use duration

Lauren Strang

I have a construction timeline that has tasks that lead up to it. The top Due Date is when the project needs to be completed by. The Days From Open need to tell me when to stat the task. (ex. first task start 90 days from 1/3/20). I also want to add a duration of how long that "90 day" task should take to complete. Basic question but how do I do this? When I add the duration with dependencies, it removes all of my dates. Is there a better template I should be working from?

Genevieve P.

Hi Lauren,

It sounds like you just need to adjust what columns are used in your sheet as the Start Date column and End Date column in your Project Settings.

You likely would want a Date Column outside of these Project Settings that has the initial Open date (so 1/3/20). Then you would need a new Start Date column to use as the Start in your Project Settings. This would be the column that has the actual Start Date, 90 days after the Open date.

To change what column is used, right click on your column and choose "Edit Project Settings". Then the Duration column would automatically calculate your End Date for you by adding those days from the new Start Date column, instead of referencing the Open date to change the End (read more about Project Settings here.)

Let me know if this would work for you. Otherwise, there may be a way to achieve what you're looking for through disabling the Dependencies from your Project Settings and using formulas instead.

Cheers!

Genevieve

Genevieve P.

Hi Lauren,

It sounds like you just need to adjust what columns are used in your sheet as the Start Date column and End Date column in your Project Settings.

You likely would want a Date Column outside of these Project Settings that has the initial Open date (so 1/3/20). Then you would need a new Start Date column to use as the Start in your Project Settings. This would be the column that has the actual Start Date, 90 days after the Open date.

To change what column is used, right click on your column and choose "Edit Project Settings". Then the Duration column would automatically calculate your End Date for you by adding those days from the new Start Date column, instead of referencing the Open date to change the End (read more about Project Settings here.)

Let me know if this would work for you. Otherwise, there may be a way to achieve what you're looking for through disabling the Dependencies from your Project Settings and using formulas instead.

Cheers!

Genevieve

Lauren Strang

Thank you! I am still having trouble because of the tasks I have under them. What are the formula options when disabling the dependencies? I was able to add a new column for the open dated. I am just having trouble with the duration time frame. I would like my duration to dictate the due date.

Genevieve P.

Hi Lauren,

Before we look into disabling the dependencies, let's see if we can figure out how to get it working with your current settings, since using dependencies & duration will dictate your due date.

Open up your edit Project Settings window (by right-clicking on a column and selecting this option from the drop down menu). It should look like my screen capture below:

You will notice that the "Start date column" is Start Date (not the Open date), and my "End date column" is selected as the Due Date. If you have these two columns selected, then when you input the duration (ex. 5d or 5 days), then it will auto-fill in the Due Date from the actual Start Date.

You will want to check that your Working Days are selected properly in this Edit window, as Duration will take that into account (for example, 5 days will be 5 working days, and will return a date that is 5 working days after the date in the Start Date column).

If you are still having trouble, please screen capture this Edit Window and post it here so we can take a look. It would also be helpful to see a full screen capture of your sheet, but block out any sensitive data.

Thanks!

Genevieve

Lauren Strang

Thank you this helped and fixed the issue with the duration. Now with the dependancies on, it deactivated Days From Open, that determines the Start Date. Ideally, I would like to know from the Open Date, (ex 90) Days From Open, what Start Date do I need to begin a task. So when I change the Days From Open, it does not change the Start Date. Please also let me know if sharing this template with you, would also be helpful. Can I do both at the same time?

Thank you again for your help!!!

Genevieve P.

In that case, you would need to turn off Dependencies, because with this turned on you cannot have a formula in the Start Date column in order to auto-return a date.

You could use the "Duration" column for your own personal records, but not have it activated in your sheet. To change this, "Edit Project Settings" like in the screen capture above, then un-check the "Dependencies Enabled" box.

Then you can use a formula that adds that original Open Date + the days in your "Days From Open" column to automatically return the Start Date for your project. Enter this into your Start Date cell:

=[Open Date]@row + [Days from Open]@row

If you want to automatically return the Due Date based on that Start Date & your new, deactivated Duration column, you could use a formula like so:

=[Start Date]@row + Duration@row

Keep in mind that your Duration column would then need to be just a number column (so 5, instead of 5d). This would also add the exact days without taking work days or holidays into account. Would this be better for your purposes? 

lizwacoal

Hi, I'm having a similar issue, where I know what the due dates are, so I want to be able to enter those and have the system to work backwards and fill in the start dates based on the duration. I understand I can do that with a formula, but if I turn off dependencies then I can no longer use the predecessor function. Is that right? Is there any way around this?

Thanks!

Genevieve P.

https://community.smartsheet.com/discussion/comment/336482#Comment_336482

Hi @lizwacoal

You're correct: you can either use Predecessors or a Formula in your date columns, but not both.

What I would suggest doing is use columns and a formula in helper, regular columns, then you can copy/paste the data into your actual Project Settings columns!

Cheers,

Genevieve

jrappe

Agree with Lizwacoal...I want the parent row features in the gantt view, but I want to be able to submit a due date via a form and it also submit a duration of 14 in a hidden field in the form and automatically set a start date. So the Gantt chart would have due date and duration and it calculate a start date. Or either allow for negative durations, which would back the date up.