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", "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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, "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
understood, thanks!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!