IF THEN formula

PMOGal

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.

Protonsponge

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 " "

PMOGal

@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?

Protonsponge

@PMOGal - is your [Finish] column set to a date column type, if not that could be the issue?

PMOGal

@Protonspounge Finish is a date column.

Protonsponge

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.

PMOGal

@Protonspounge Attached is a screenshot.

DaysToCompletion.png

Protonsponge

@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.

Paul Newcome

If you have confirmed that the [Finish] column is in fact a date type column, how exactly is that column being populated?

PMOGal

@Protonspounge @Paul Newcome The Finish column is a Date column. I usually click on a field and choose a date from the calendar.

Protonsponge

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,

Paul Newcome

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?

PMOGal

@Paul Newcome Everything that is "Pending" status returns "Not a Date" in that column. "In Progress" and "Complete" return a blank.

Paul Newcome

And what about the formula with NETDAYS in each of those rows?

PMOGal

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".

PMOGal

@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")