IF THEN formula
Hi. I need help with If/Then.
If a Column called "Status" is not "Complete" or Blank, then, I would my Days to Completion Column to calculate the days to Expected Finish. I think the calculation is =Finish@row-TODAY().
Thanks.
Answers
-
Hello @PMOGal ,
Would this formula work for you?
=IF(Status@row <> "complete", NETDAYS(TODAY(), [Finish Date]@row), "N/A")
If the status in [Status] is anything by complete it will calculate the number of days from today to the [Finish Date]@row… If the [Status] is complete it will put N/A. You can have it be blank if you like by replacing "N/A" with " "
-
@Protonspounge … I had to modify to the formula to reflect my columns to:
=IF(Status@row <> "Complete", NETDAYS(TODAY(), Finish@row), "N/A")
Smartsheet tells me my formula is #INVALID DATA TYPE.
What do I need to change? -
@PMOGal - is your [Finish] column set to a date column type, if not that could be the issue?
-
@Protonspounge Finish is a date column.
-
Hello @PMOGal, sorry this isn't working for you… any chance of a quick screen shot to see why this isn't working?
For the demo I made the [Status] is a Text/Number column, [Finish Date] is a date columns and my days to completion is a Text/Number column. If you are set up like this it should be working.
-
@Protonspounge Attached is a screenshot.
-
@Paul Newcome - Would you have any thoughts on why this formula isn't working for @PMOGal, I was able to get this working in a demo sheet but PMOGal is getting #INVALID DATA TYPE.
-
If you have confirmed that the [Finish] column is in fact a date type column, how exactly is that column being populated?
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!
-
@Protonspounge @Paul Newcome The Finish column is a Date column. I usually click on a field and choose a date from the calendar.
-
Hello @PMOGal,
To make a column you need to double click the column and change it to a date column. If you set the [Finish] column to a date column I think it will work for you,
-
Lets try this…
Insert a temporary column for troubleshooting and then use the following column formula:
=IF(NOT(ISDATE(Finish@row)), "Not a Date")
Then apply a filter to the sheet to show rows where this temporary column is not blank. What do we get?
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!
-
@Paul Newcome Everything that is "Pending" status returns "Not a Date" in that column. "In Progress" and "Complete" return a blank.
-
And what about the formula with NETDAYS in each of those rows?
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!
-
Sorry, some more detail and then the answer to your question.
If Status = "Pending" , the Is It a Date column returns a number in the Days to Completion column, if dates are associated with "Pending".
-
@Paul Newcome how do I add Status <> "Complete" or "Pending" to this formula? I don't really care about Pending items.
=IF(Status@row <> "Complete", NETDAYS(TODAY(), Finish@row), "N/A")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!