Formula for a Due Date

Sirius_200

My goal is to make Smartsheet calculate an End Date for a task.

The End Date of this task depends on these two variable:

Variable #1: 100% Project Completion Due Date

Variable #2: Duration of the Project

The End Date for this task is half way to 100% Project Completion Due Date.

What is the best way to make Smartsheet calculate this End Date?

I think there are two possible methods for me:

Method #1: Use "Predecessors"

Method #2: Use formula in "End Date"

But I don't know how to use "Predecessors" and formula in "End Date" to make this work.

Does anyone have a suggested solution?

Mike TV

@Sirius_200

Does this work?

Project Duration (days) column formula:

=NETDAYS([Project Start Date]@row, [100% Project Completion Date]@row)

Task End Date column formula:

=[100% Project Completion Date]@row - ([Project Duration (days)]@row / 2)

Sirius_200

@Mike TV

Is there a way I can use that formula in a column type of "Date/Time"?

I tried to use that formula / logic in a cell within a column type of "Date/Time" but the formula shows up as text; the formula is not activated.

Mike TV

@Sirius_200

Either what you pasted has quotation marks around it or it doesn't start with an equal sign (=) and needs to. If it looks like neither of those situations are the case, can you please provide an image of it in your sheet?

Sirius_200

@Mike TV

Yes, here's a screen shot:

Thank you for your help!

Mike TV

@Sirius_200

It looks like you're putting it in the wrong cell aren't you? Wouldn't you want this formula to be in the End Date column? With it in the End Date column and assuming the dark blue bar is row 1 on your sheet, your formula would be:

=WORKDAY([End Date]1, -(Duration1 / 2))

Sirius_200

@Mike TV It doesn't matter if I put the formula in the "Start Date" or "End Date" because the start date/end date should be the same. I put this formula per your suggestion, but the formulas in the cells are not converting to a date. It remains as text as you can see below.

Is it because my column type is "Date/Time"?

Mike TV

@Sirius_200

It shouldn't matter if your column is a Date or a Text/Number type column. Either way it shouldn't be showing that as text. The equal sign at the start should force SmartSheet into a formula mode. Unless you've got a space before the = then I'm not sure what you're doing wrong. Someone else with experience with this being messed up like this will have to assist why you're unable to create formulas.

Sirius_200

@Mike TV Got it! Thank you for your help! Appreciate it!

Sirius_200

Hi @Genevieve P. ! May I ask for your help? Do you know what I might be doing wrong? Why is the = sign not activating my formula?

Genevieve P.

Hi @Sirius_200

There are specific columns in Smartsheet that cannot have a formula in them.

Based on your Predecessor column, it looks like you have Dependencies enabled in your Project Settings. This means that you will not be able to have a formula in the Start Date or End Date columns (as they have their own back-end formulas applied in order to update as you make changes to the details in those rows). See: Areas where formula use is restricted

Cheers,

Genevieve

Sirius_200

@Genevieve P. Thank you! I guess I have to figure out another way to do this. Thank you!