formula not working

Yashwant Singh

hi Team,

I have two date columns and based on date column final status column. and applying simple formula .

if (col1<=col2, "Green","Red")

but in case end user enter NA in both the column, output is "Green"

and col1 is NA and col2 is N/A then output is "Red"..

why..? please suggest .

Malaina Hudson

@Yashwant Singh ,

This is a fun one. I've never encountered this particular situation before, but both NA and N/A are alpha strings in Smartsheet. They are not, however, equal. I can't say for certain if the < indicator is being looked at in your formula, but the way it's currently written, the IF test is for < or = between col1 and col2. Since NA is not equal to N/A, the = condition is false. It's possible that < and are not valid comparisons in Smartsheet for alpha strings. When I tested your formula with numeric values, the formula works as you have it written.

Yashwant Singh

Hi @Malaina Hudson thanks for your response..

What I do then ..please suggest..

Malaina Hudson

@Yashwant Singh ,

In comparing date column to date column, your formula is good, but the allowance of NA, N/A, and other text strings is what's giving you an unexpected result. After digging around a bit, I see that when the date fields are populated only with dates, the formula works as expected. When populating a date field with general text, it returns an #INVALID OPERATION error. And when comparing NA to N/A, how the test is read depends on the position of the /.

I suspect that this is because N/A has a predefined meeting in Smartsheet much like it does in Excel, but NA is simply a alpha string. Since they are not equal, this test demonstrates that Smartsheet values N/A as <= NA. I'm secretly hoping someone from Smartsheet chimes in here to validate my theory because I'm not positive about this, but it's what the data test reflects.

If you're trying to rule out false positive or false negative caused by alpha strings, you could try a few things:

• Setting the column properties on your date ranges to disallow anything except dates. It won't rule out blank fields, but I'm not sure how sensitive your comparison is to blanks.

• Adding a validation in front of the IF statement to test for NA in the date field before moving to the IF clause.

• Adding a helper column with an IF clause to test for NA or N/A in either date field, then testing for a non-null before finalizing your comparison. It's 2-3 nested IFs deep, but depending on how you're using this data, it could be a suitable approach.

In my daily use, I rarely compare two date columns because we frequently have HH:MM:SS stamps included in them, and the time stamps interfere with other things we do with the dates. Instead, I use helper columns to measure the time between dates, and then compare to day targets, e.g., days to invoice, days to process, days outstanding. These KPIs allow the operators to clearly measure progress on their improvement projects because you can easily tell if 5 days to invoice is <>= the target of 7 days. And if we move from 5.3 days to invoice to 4.7 days to invoice, they can clearly measure that their improvement is roughly 11%. Red and Green are more subjective, and harder to demonstrate the impact of the actions.

Hope this helps.