## Please advise if you can help me convert this Excel formula to a Smartsheet formula.

#### Welcome to the New Smartsheet Online Community

You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

# Please advise if you can help me convert this Excel formula to a Smartsheet formula.

I keep getting #UNPARSEABLE when using the following formula:

=IF([CUSTODY DATE]7>1/1/2000,IF(\$MILEAGE\$2<=([CUSTODY DATE]7+2),(125+75+40),(\$MILEAGE\$2-([CUSTODY DATE]7+2)*15+(125+75+40)), )

I was told to make this correction, however, I am still having the same trouble:

Based on the IF formula provided, \$ MILEAGE \$ 2 seems to be causing the #UNPARSEABLE error. If this is a column, you will need to change it to [MILEAGE](row number or @row).

Any help is appreciated.

• Try changing your date to fit the DATE function. Smartsheet does not recognize "1/1/2000" as a date. It recognizes it as 1 divided by 1 divided by 2000. To use dates in Smartsheet formulas, the DATE function is required.

DATE(yyyy, mm, dd)

=IF([CUSTODY DATE]7>DATE(2000, 1, 1),IF(\$MILEAGE\$2<=[CUSTODY DATE]7+2,125+75+40,(\$MILEAGE\$2-([CUSTODY DATE]7+2)*15+(125+75+40)), )

There is also an extra comma towards the end that is causing the actual #UNPARSEABLE error.

Here is the formula with the DATE function included and the comma (and some unnecessary parenthesis) removed:

=IF([CUSTODY DATE]7 > DATE(2000, 1, 1), IF(\$MILEAGE\$2 <= [CUSTODY DATE]7 + 2,125 + 75 + 40, \$MILEAGE\$2 - ([CUSTODY DATE]7 + 2) * 15 + (125 + 75 + 40)))

• You do have to correct your reference to the MILEAGE column.

Looks like you're using a nested IF formula (an IF within an IF) and didn't complete the first IF structure.

Your value if true is another IF which appears complete, it's got an expression, a value if true, and a value if false.

=IF([CUSTODY DATE]2 > 1 / 1 / 2000, IF(MILEAGE2 <= ([CUSTODY DATE]2 + 2), (125 + 75 + 40), (MILEAGE2 - ([CUSTODY DATE]2 + 2) * 15 + (125 + 75 + 40)),

Your value if False on the first IF is missing:

=IF([CUSTODY DATE]2 > 1 / 1 / 2000, IF(MILEAGE2 <= ([CUSTODY DATE]2 + 2), (125 + 75 + 40), (MILEAGE2 - ([CUSTODY DATE]2 + 2) * 15 + (125 + 75 + 40)), 12)), #Value if False should be here)

From the help site:

IF Function

Evaluates a logical expression and returns one value when true or another when false.

Sample Usage

IF([Due Date]1 > [Due Date]2, "Date 1 is Larger", "Date 2 is Larger")

Syntax

IF(logical_expressionvalue_if_true[value_if_false])

• logical_expression—The expression to evaluate. Must be true or false.
• value_if_true—The value or formula to return if the logical expression is true.
• value_if_false—[optional] The value or formula to return if the logical expression is false. If omitted, a blank value is returned.

• @andyrami Having an IF function nested in the "value if true" portion of another IF function is similar to using "IF(AND(..................),"

I do this quite frequently if I am using multiple variables associated to the same variable. Instead of

=IF(AND(variable 1 = 1, variable 2 = 2), 2, IF(AND(variable 1 = 1, variable 3 = 3), 3))

I just use

=IF(variable 1 = 1, IF(variable 2 = 2, 2, IF(variable 3 = 3, 3)))

Because I am repeating "variable 1" for multiple portions of the remaining IF statements, doing it this way saves a few key strokes. It doesn't seem like much of a difference here, but when you start getting into more complex nested IF's, it can actually be a huge lifesaver.

For example...

I have a very long series of nested IF's, but I only want them to run if a certain cell contains a date. Instead of going through and changing all of my IF statements to have an AND statement and adding that in, I can just wrap the whole thing in a single IF formula that says

=IF(ISDATE([Date Column]@row), run this other long and complex nested IF)

By leaving the third portion of this new IF statement out, it will remain blank if that first IF is not true.

• Here is a screenshot of the sheet, as I am still getting the error.

• @Yolanda Brown-Mccutchen Can you also post the exact formula that you are now using?

• Hi Paul,

I tried the formula you gave but it's not working. I must be doing something wrong. Please be patient with me as this is my first time working with Smartsheet, as well as formulas.

• No worries. Welcome to Smartsheet! Formulas can be rather daunting especially when first starting out.

What is the column type for the MILEAGE column?

• Ok. And what type of column is the [Custody Date] column?

• Ok. So this next step goes back to the DATE function. Because the data in the \$MILEAGE\$2 cell is if the text/number type, we need to convert it into a date for your formula to run calculations off of.

I am going to assume the format for that data is always going to be mm/dd/yy. If there is a possibility of single digit day or month or four digit year, please let me know so we can rewrite to accommodate. I will also assume that the year is 2000 or greater. Again... Please let me know if we need to accommodate anything else.

To get the year...

=VALUE("20" + RIGHT(\$MILEAGE\$2, 2))

To get the month...

=VALUE(LEFT(\$MILEAGE\$2, 2))

And the day...

=VALUE(MID(\$MILEAGE\$2, 4, 2))

Now we drop these into the appropriate portions of the DATE function...

=DATE(VALUE("20" + RIGHT(\$MILEAGE\$2, 2)), VALUE(LEFT(\$MILEAGE\$2, 2)), VALUE(MID(\$MILEAGE\$2, 4, 2)))

Now we take the above and use it to replace ever reference to \$MILEAGE\$2...

=IF([CUSTODY DATE]7 > DATE(2000, 1, 1), IF(DATE(VALUE("20" + RIGHT(\$MILEAGE\$2, 2)), VALUE(LEFT(\$MILEAGE\$2, 2)), VALUE(MID(\$MILEAGE\$2, 4, 2))) <= [CUSTODY DATE]7 + 2,125 + 75 + 40, DATE(VALUE("20" + RIGHT(\$MILEAGE\$2, 2)), VALUE(LEFT(\$MILEAGE\$2, 2)), VALUE(MID(\$MILEAGE\$2, 4, 2))) - ([CUSTODY DATE]7 + 2) * 15 + (125 + 75 + 40)))

• Thank you. I will try this shortly and let you know if it works.