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

Yolanda Brown-Mccutchen

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.

Paul Newcome

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

andyrami

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.

So your first if starts: =IF([CUSTODY DATE]7>1/1/2000, (your expression)

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.

Paul Newcome

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

Yolanda Brown-Mccutchen

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

Paul Newcome

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

Yolanda Brown-Mccutchen

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.

Paul Newcome

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

What is the column type for the MILEAGE column?

Yolanda Brown-Mccutchen

Text/Number?

Paul Newcome

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

Yolanda Brown-Mccutchen

Date

Paul Newcome

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

Yolanda Brown-Mccutchen

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