Formula Question

ecarrero

Hello keep getting a syntax error. Formula I'm using is:

=IF(AND(ISDATE([Start]1), ISNUMBER([Completion Before Event Date]@row)), [Start]1 - [Completion Before Event Date]@row & "D", "")

What I'm wanting the formula to do is refer to the event date listed on row 1 in the "Start" column as the main date. The below columns should subtract the days in the "Completion Before Event Date" column and determine the "Deadline" column date for each row.

Secondly, the other formula should then look at the "deadline" column date and subtract the "Days Duration" column to then determine the "Start" date" for each task.

=IF(AND(ISDATE(Deadline@row), ISNUMBER([Days Duration]@row)), Deadline@row - [Days Duration]@row, "")

If the days duration is blank it can return the deadline date.

Ideas on how to fix this? Thanks!

Paul Newcome

I'm not sure what exactly you are trying to accomplish with the "& "D"" portion.

=IF(AND(ISDATE([Start]1), ISNUMBER([Completion Before Event Date]@row)), [Start]1 - [Completion Before Event Date]@row & "D", "")

ecarrero

The & "D" part is used to indicate that the value in the "Completion Before Event Date" column represents a number of days. But if there's a different recommendation, I'm open to it!

Mark Safran

@ecarrero if you're trying to append character "D" to the string, you just have to use '+', not '&'.

But if you do this, you won't then be able to use the resultant value in your second formula to compare against the deadline date.

Best bet is to just remove the '& "D"' from you formula. And if you really want the value to have that trailing character designation, add in a helper column that just takes =Duration@row + " D"

Paul Newcome

I would suggest removing it entirely. Adding a whole number to a date is automatically assumed to be a number of days when working in Smartsheet.

=IF(AND(ISDATE([Start]1), ISNUMBER([Completion Before Event Date]@row)), [Start]1 - [Completion Before Event Date]@row, "")

ecarrero

Thanks @Paul Newcome ! It appears to be working but when trying to convert the deadline to a column formula, I get a syntax error. Separately, when I try to convert the start column to a column formula it makes both of these columns data disappear.

Paul Newcome

Column formulas only work with "@row" references. If you want your formula to reference a cell that is on a different row, you will not be able to set it as a column formula.

ecarrero

understood, thanks!

Paul Newcome

Happy to help. 👍️