Formula Question
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!
Answers
-
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", "")
-
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! -
@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"
-
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, "")
-
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.
-
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.
-
understood, thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!